Re: INSERT RETURNING rule for joined view

On Mon, Jun 1, 2009 at 2:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Sava Chankov <sava(dot)chankov(at)gmail(dot)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