Skip site navigation (1) Skip section navigation (2)

Default values, inserts, and rules...

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Default values, inserts, and rules...
Date: 2002-08-21 05:37:46
Message-ID: 20020821053746.GC22519@ninja1.internal (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
I'm working on creating a DB schema that has full auditing/logging and
have run across a sticking point that's 'causing me a ton of grief.
There are two bits going on in this example:

1) To get the current value from a sequence, I have to use
   CURRVAL(seq_name) in the actual rule.  Not that big of a deal, but
   kind of a PITA that I can't just use NEW.s.

2) When the rule does the insert into the t_log table, NEW.c1 doesn't
   contain the default value of 42.  How can I do this?  I don't want
   to force the application to specify the default values for
   everything.

CREATE TABLE t (
  s SERIAL NOT NULL,
  c1 INT DEFAULT 42 NOT NULL,
  c2 CHAR(1) NOT NULL);
CREATE TABLE t_log (
  s INT NOT NULL,
  c1 INT NOT NULL,
  c2 CHAR(1) NOT NULL);
CREATE RULE t_ins AS ON INSERT TO t DO
  INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),NEW.c1,NEW.c2);
INSERT INTO t (c2) VALUES ('a');
ERROR:  ExecAppend: Fail to add null value in not null attribute c1

I think this was brought up a while ago...  Tom, in response to your
question, moving this into the parser I think would solve the above
behaviors that I'd classify as incorrect.  Am I wrong or missing some
way of getting the desired behavior?

http://archives.postgresql.org/pgsql-bugs/2001-10/msg00145.php


-sc

-- 
Sean Chittenden
sean(at)ruby-lang(dot)org
seanc(at)FreeBSD(dot)org

Responses

pgsql-bugs by date

Next:From: Masaru SugawaraDate: 2002-08-21 12:25:25
Subject: Re: Default values, inserts, and rules...
Previous:From: fredrik chabotDate: 2002-08-20 21:36:25
Subject: Re: inconsistend performance

pgsql-general by date

Next:From: tonyDate: 2002-08-21 07:16:20
Subject: Re: Sun Pushing MySQL but Mentions PostgreSQL
Previous:From: scott.marloweDate: 2002-08-21 05:26:04
Subject: Re: PostgreSQL and MySQL in ZDNet article...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group