Re: Using results from INSERT ... RETURNING - Mailing list pgsql-hackers

On Mon, Sep 28, 2009 at 3:19 PM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
> Robert Haas wrote:
>>
>> Can you at least take a stab at it?  We can fix your grammar, but
>> guessing what's going on without documentation is hard.
>
> With some help from David Fetter, I took another try at it.  I hope
> someone finds this helpful.  I'm happy to answer any questions.

Thanks.  I read through this patch some more tonight and I guess I am
a bit confused about what it accomplishes.  AIUI, the point here is to
lay the groundwork for a future patch to allow writeable CTEs, and I
guess I'm not understanding how it's going to do that.

rhaas=# create table project (id serial primary key, name varchar not
null);NOTICE:  CREATE TABLE will create implicit sequence
"project_id_seq" for serial column "project.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"project_pkey" for table "project"
CREATE TABLE
rhaas=# create table shadow (id integer not null primary key, name
varchar not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"shadow_pkey" for table "shadow"
CREATE TABLE
rhaas=# create rule clone as on insert to project do also insert into
shadow (id, name) values (NEW.id, NEW.name);
CREATE RULE
rhaas=# insert into project (name) values ('Writeable CTEs') returning id;id
---- 1
(1 row)

INSERT 0 1
rhaas=# explain insert into project (name) values ('Writeable CTEs')
returning id;                  QUERY PLAN
------------------------------------------------Insert  (cost=0.00..0.01 rows=1 width=0)  ->  Result  (cost=0.00..0.01
rows=1width=0) 
Insert  (cost=0.00..0.01 rows=1 width=0)  ->  Result  (cost=0.00..0.01 rows=1 width=0)
(5 rows)

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.

What am I missing?

...Robert