Re: Using results from INSERT ... RETURNING

On Fri, Oct 2, 2009 at 7:37 AM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> Robert Haas wrote:
>>
>> Now the point here is that I eventually want to be able to write
>> something like this:
>>
>> with foo as (insert into project (name) values ('Writeable CTEs')
>> returning id) select * from foo;
>>
>> ...but how does this get me any closer?  It seems to me that the plan
>> for THAT statement has to be a CTE scan over top of BOTH of the
>> inserts, but here I have two insert nodes that comprise two separate
>> plans.  The DML node, as presently implemented, supports a list of
>> plans, but they all have to be of the same type, so it's really only
>> useful for handling append, and as previously discussed, it's not
>> clear that the proposed handling is any better than what we already
>> have.
>
> I don't think you should be able to do this.  I'm not too familiar with
> rules, but in your example the rule doesn't modify the output of the
> INSERT .. RETURNING so I think it shouldn't do that here either.  How I
> see it is that in your example the INSERT INTO shadow would be added to
> the top level instead of the CTE and the plan would look something like
> this:
>
> ------------------------------------------------
>  CTE Scan on foo  (cost=0.01..0.03 rows=1 width=4)
>   CTE foo
>     ->  Insert  (cost=0.00..0.01 rows=1 width=0)
>           ->  Result  (cost=0.00..0.01 rows=1 width=0)
>
>  Insert  (cost=0.00..0.01 rows=1 width=0)
>   ->  Result  (cost=0.00..0.01 rows=1 width=0)
>
> so you would get the RETURNING output from the CTE and the INSERT to the
> shadow table would be executed separately.

Yeah, I think you're right.

> I'm not saying that we don't
> want to provide the means to do this, but writeable CTEs alone aren't
> meant to handle this.

Well, I think a patch to implement writeable CTEs is probably going to
have to handle this case - I don't think we can just ignore rewrite
rules when processing a CTE. But it does seem to be beyond the scope
of the current patch.

I'm going to go ahead and mark this Ready for Committer. Thanks for
your patience.

...Robert