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
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