Re: Using results from INSERT ... RETURNING

From: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using results from INSERT ... RETURNING
Date: 2009-10-02 11:37:20
Message-ID: 4AC5E5F0.8010405@cs.helsinki.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Regards,
Marko Tiikkaja

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bernd Helmle 2009-10-02 12:19:40 Re: TODO item: Allow more complex user/database default GUC settings
Previous Message Roger Leigh 2009-10-02 11:21:35 Re: Unicode UTF-8 table formatting for psql text output