View updating and nextval() workaround - will this ever break?
От | Richard Huxton |
---|---|
Тема | View updating and nextval() workaround - will this ever break? |
Дата | |
Msg-id | 454714B8.4030605@archonet.com обсуждение исходный текст |
Ответы |
Re: View updating and nextval() workaround - will this ever break?
|
Список | pgsql-hackers |
Basically, I'm wondering if anyone can see a problem with my standard workaround to the macro-expansion-vs-nextval problem with view. I can't see how PG changes might break it, but I might be using it in a presentation to others so thought I'd best check. BEGIN; CREATE TABLE foo (f_id serial, f1 int4, PRIMARY KEY (f_id)); CREATE TABLE bar (f_id int4 NOT NULL REFERENCES foo, b1 int4, PRIMARY KEY (f_id,b1)); -- This one suffers from the macro-expanding-nextval problem -- CREATE VIEW foobar_bad AS SELECT f_id, f1, b1 FROM foo NATURAL JOIN bar; CREATE RULE foobar_bad_ins AS ON INSERT TO foobar_bad DO INSTEAD (INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), NEW.f1);INSERT INTO bar (f_id, b1) VALUES (currval('foo_f_id_seq'),NEW.b1); ); -- This one doesn't -- CREATE VIEW foobar_good AS SELECT f_id, f1, b1 FROM foo NATURAL JOIN bar; CREATE FUNCTION foobar_ins_fn(p_f1 int4, p_b1 int4) RETURNS void AS $$ BEGIN INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), p_f1); INSERT INTO bar (f_id, b1) VALUES (currval('foo_f_id_seq'),p_b1); END; $$ LANGUAGE plpgsql; CREATE RULE foobar_good_ins AS ON INSERT TO foobar_good DO INSTEAD SELECT foobar_ins_fn(NEW.f1, NEW.b1); -- Bad version... INSERT INTO foobar_bad (f1,b1) SELECT generate_series, generate_series+10 FROM generate_series(1,3); SELECT * FROM foobar_bad; DELETE FROM bar; DELETE FROM foo; -- Good version... INSERT INTO foobar_good (f1,b1) SELECT generate_series, generate_series+10 FROM generate_series(1,3); SELECT * FROM foobar_bad; DELETE FROM bar; DELETE FROM foo; COMMIT;
В списке pgsql-hackers по дате отправления: