Обсуждение: INSERT RETURNING rule for joined view
I have a view that joins several tables and want to create unconditional INSERT RETURNING rule for it. I succeeded by specifying the RETURNING clause for the first INSERT in the rule, casting NULL for columns that are not present in that table to the correct type:
CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE b (id INTEGER REFERENCES a, surname TEXT);
CREATE VIEW j AS (SELECT a.id,a.name, b.surname FROM a NATURAL JOIN b);
CREATE RULE _insert AS ON INSERT TO j DO INSTEAD(
INSERT INTO a (id,name) VALUES (NEW.id, NEW.name) RETURNING id, name, NULL::text;
INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) );
Is there a way to make RETURNING return all view columns? If not, I'd like to submit a documentation patch that clarifies the behaviour of CREATE RULE for INSERT RETURNING in that case.
--
cheers,
Sava Chankov
CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE b (id INTEGER REFERENCES a, surname TEXT);
CREATE VIEW j AS (SELECT a.id,a.name, b.surname FROM a NATURAL JOIN b);
CREATE RULE _insert AS ON INSERT TO j DO INSTEAD(
INSERT INTO a (id,name) VALUES (NEW.id, NEW.name) RETURNING id, name, NULL::text;
INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) );
Is there a way to make RETURNING return all view columns? If not, I'd like to submit a documentation patch that clarifies the behaviour of CREATE RULE for INSERT RETURNING in that case.
--
cheers,
Sava Chankov
Sava Chankov <sava.chankov@gmail.com> writes: > Is there a way to make RETURNING return all view columns? Something like CREATE RULE _insert AS ON INSERT TO j DO INSTEAD( INSERT INTO a (id,name) VALUES (NEW.id, NEW.name); INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) RETURNING id, (SELECT name FROM a WHERE id = b.id) as name, surname ); This only really works if the insert specifies "id" explicitly, which is not amazingly desirable. That's not the fault of the RETURNING though, but of the repeat reference to NEW.id which might be a volatile expression (ie, nextval()). In some cases it's okay to hack around that by using currval() the second time, but that just trades off one unexpected behavior for a different one ... regards, tom lane
On Mon, Jun 1, 2009 at 2:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Sava Chankov <sava.chankov@gmail.com> writes: >> Is there a way to make RETURNING return all view columns? > > Something like > > CREATE RULE _insert AS ON INSERT TO j DO INSTEAD( > INSERT INTO a (id,name) VALUES (NEW.id, NEW.name); > INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) > RETURNING id, (SELECT name FROM a WHERE id = b.id) as name, surname > ); > > This only really works if the insert specifies "id" explicitly, which is > not amazingly desirable. That's not the fault of the RETURNING though, > but of the repeat reference to NEW.id which might be a volatile > expression (ie, nextval()). In some cases it's okay to hack around that > by using currval() the second time, but that just trades off one > unexpected behavior for a different one ... Here's what I do currently. It's pretty dirty. create or replace rule ins_foo as on insert to foo do instead ( select add_foo(new); -- takes and returns type foo update foo set foo_id = foo_id where node_id = currval('node_seq') returning *; ); This has a slight advantage over the OP's approach...it scales out better to more colums at the cost of an update vs a delete. The actual inserts are pushed out to a function because it's easier to deal with intangibles there. With 8.4 table expressions, it's tempting to write: create or replace rule ins_foo as on insert to foo do instead ( with n as (select add_foo(new) as f) insert into foo select null where false returning (n).f.*; ); This fails because we can't yet use insert stmts in CTE. Another approach which is worth mentioning is this: create or replace rule ins_foo as on insert to foo do instead ( update foo set foo_id=foo_id where false returning *; with n as (select add_foo(new) as f) select (f).* from n; ); Having bluffed our way past the RETURNING restriction, we get a updateless rule that returns all the fields of the target view, always (regardless if you do or don't use returning in the insert stmt that triggers the rule). This causes problems with insert..into plpgsql expressions and possibly other things. merlin