Re: Using results from INSERT ... RETURNING

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> On Sat, Jul 18, 2009 at 6:25 PM, Merlin Moncure<mmoncure(at)gmail(dot)com> wrote:
>> Being able to use 'returning' in a subquery is probably the #1 most
>> requested feature for postgresql (it's also a todo). Solving it for
>> 'with' queries is a nice step in the right direction, and sidesteps
>> some of the traps that result from the general case.

> ah! that's why i asked: 'if we will support this, shouldn't we
> supporting INSERT RETURNING inside subqueries too?'

We've been over that: when will you fire triggers? What happens if the
outer query doesn't want to read the whole output of the DML command,
or wants to read it more than once?

If the DML command is in a single-evaluation WITH clause at the top
level of the command, then it's reasonable to identify the outer
command's own begin and end as the times to fire triggers; and there is
no issue about partial or repeated evaluation. If it's in a subquery
then things get much messier and more poorly defined.

Note that it can't just be "a WITH clause". It has to be one at the top
query level, or the problem comes right back.

regards, tom lane