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

> This is something for 8.3?

Well, if we put it off till 8.3 we are going to have to write something
pretty lame in the documentation about views not working with RETURNING.

After some further thought, I think we could make it work if we treat
XXX RETURNING as a distinct rule event type and make the following
restrictions (which are exactly analogous to the restrictions for ON
SELECT rules) for ON XXX RETURNING rules:

* there can be only one ON XXX RETURNING rule per relation;

* it has to be an unconditional DO INSTEAD rule;

* it has to have a single action that is the same type of operation
it's replacing (or maybe we could allow any RETURNING command?);

* the RETURNING list has to match the column datatypes of the view.

Perhaps later we could support more stuff, but this particular case
would cover simple needs and it doesn't seem like something we'd
regret supporting later. The main thing we'd be setting in stone
is that the RETURNING commands require a different rule type, which
is a bit tedious but I don't really see a good way around it.
(Hopefully the updatable-views patch will soon save people from
having to write all these rules out by hand, anyway.)

I don't have a patch yet, but preliminary experimentation suggests
that the rewriter will Just Work, and all we'll need is straightforward
boilerplate code to support the additional possible values of
pg_rewrite.ev_type --- so probably less than a day's work.

Thoughts, objections?

regards, tom lane