On Mon, 2008-07-07 at 12:16 +0200, Mathias Palm wrote: > CREATE TABLE data (id SERIAL, title VARCHAR); > CREATE TABLE data_copy(id INT4, title VARCHAR); > CREATE RULE make_copy AS ON INSERT TO data DO INSERT INTO data_copy > (id,title) VALUES (NEW.id, NEW.title); > INSERT INTO data (title) VALUES ('test'); > > database=# SELECT * FROM data; > id | title > ----+------- > 1 | test > (1 Zeile) > > database=# SELECT * FROM data_copy; > id | title > ----+------- > 2 | test > (1 Zeile) >
This is a feature, not a bug. To get the kind of behavior you want, you need to either use a trigger, or have the rule call a function that inserts the same values into both tables. SERIAL implicitly calls nextval() on a sequence to get the default value to insert. The rule rewrites the query into two queries, and each query calls nextval(), and each call of nextval() returns a different value. One way to think about it is that a rule doesn't save any values away in variables. Functions do save the arguments as variables on the stack, and those variables can be used multiple times, so that's why calling a function works. Regards, Jeff Davis -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs