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
[EMAIL PROTECTED]
[EMAIL PROTECTED]
msg04658/pgp00000.pgp
Description: PGP signature
