Re: insert/update/delete returning and rules

"Jaime Casanova" <systemguards(at)gmail(dot)com> writes:
> On 8/15/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What are you testing exactly? I think this recent fix might be
>> relevant:
>> http://archives.postgresql.org/pgsql-committers/2006-08/msg00299.php

> i have tested again against current HEAD... what i do is to create a
> table and then a view against that table... then the rules... you can
> use the exact case i posted earlier...

Oh, I'm thinking about the wrong problem. What you've got is INSERT
RETURNING on a view, and that's being trapped by an ON INSERT DO INSTEAD
rule, which rewrites it into an INSERT. Without RETURNING.

Right offhand this seems like a can of worms. I think it would probably
be a really bad idea for the rewriter to try to automatically transpose
the RETURNING clause into the rewritten query. In simple cases it might
be able to get it right, but in complicated cases I see no hope.

I'm tempted to suggest that the RETURNING commands might need to be
separate rule events, and that to support this you'd need to write
an additional rule:

CREATE RULE r1 AS ON INSERT RETURNING TO myview DO INSTEAD
INSERT ... RETURNING ...

where the RETURNING clause in the rule body would be expected to produce
exactly the set of columns of the view, and then the rewriter could hack
that up as input to whatever RETURNING list was given in the source
query.

But even this seems like it would fail in complicated cases. What if
the view is a join, and your ON INSERT rule inserts into two different
underlying tables in two commands? If you need fields from both tables
to generate a full RETURNING list then there's no apparent way to make
it work.

Ugh. Any ideas out there?

regards, tom lane