Re: Using results from INSERT ... RETURNING

On Fri, Jul 17, 2009 at 10:42:02AM +0300, Peter Eisentraut wrote:
> On Tuesday 07 July 2009 23:31:54 Marko Tiikkaja wrote:
> > Here's a patch(WIP) that implements INSERT .. RETURNING inside a CTE.
> 
> Could you supply some test cases to illustrate what this patch accomplishes?

postgres:54321=# CREATE TABLE t(i INTEGER);
CREATE TABLE

postgres:54321=# WITH t1 AS (   INSERT INTO t VALUES (1),(2),(3)   RETURNING 'INSERT', i
) SELECT * FROM t1;?column? | i 
----------+---INSERT   | 1INSERT   | 2INSERT   | 3
(3 rows)

Not working yet:

CREATE TABLE t(i SERIAL PRIMARY KEY);
NOTICE:  CREATE TABLE will create implicit sequence "t_i_seq" for serial column "t.i"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE

postgres:54321=# WITH t1 AS (INSERT INTO t VALUES
(DEFAULT),(DEFAULT),(DEFAULT) RETURNING 'INSERT', i) SELECT * FROM t1;
ERROR:  unrecognized node type: 337

Also planned, but no code written yet:

UPDATE ... RETURNING
DELETE ... RETURNING

UNION [ALL] of each of INSERT, UPDATE, and DELETE...RETURNING inside the
CTE, analogous to recursive CTEs with SELECT.

Way Out There Possibility: mix'n'match recursion.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate