Default values, inserts, and rules...

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@ruby-lang.org
seanc@FreeBSD.org