INSERT .... RETURNING

From: Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: INSERT .... RETURNING
Date: 2008-11-05 07:41:27
Message-ID: 1225870887.13207.84.camel@zorro.isa-geek.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Everybody,

Forgive my sarcasm below, but I just *adore* postgres for years, now. I
hope it's all natural with this level of emotions to be deeply hurt when
the object of attraction is (to quote HHTTG by Douglas Adams): " ....
almost, but not quite entirely unlike tea".

I've just upgraded to v8.3.4 ... since eventually it does have
INSERT ... RETURNING extention to the SQL standard.

The documentation was quite encouraging: "if INSERT contains RETURNING,
the result is similar to SELECT....".

So, first thing after upgrade I tried:
+--------------------------------
|CREATE TABLE test1 (id serial, info text);
|CREATE TEMP TABLE test2 AS INSERT INTO test1 (info) VALUES
( random()::text );
+-----------------------------------

I also tried other scenarios with "INSERT... RETURNING ..." where SELECT
could be used as subquery - all this to no avail.

At this point my sad conclusion is, that "similar to SELECT" behavior is
limited to client-side server API, not to SQL per se.

The question is: Is this a feature or a bug? (by "a bug" here I mean: an
*unintended* omission).

Or may be there is currently some way to capture the "RETURNING" into
SQL commands sequence (meaning, back into the database)? And I just
haven't discovered the proper way from the docs?? If so, someone pls
point me to those docs.

I'm rising this issue, since this really is a long awaited feature and I
was really waiting for it - particularly after I've faced some badly
entangled schemas in the past, which could easily be cut through using
"CREATE TEMP TABLE .... AS INSERT...".

Is there any way to get SQL-level functionality like this, with
"current" v8.3.4 (or similar)? Or is there any hope for such
functionality to show up in future releases (I believe, it's only a
syntax matter now, all the internals look like already implemented)?

Thenx,

-R

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomasz Ostrowski 2008-11-05 08:37:42 Re: Storage location of temporary files
Previous Message Martijn van Oosterhout 2008-11-05 07:31:20 Re: [Fwd: Re: GEQO randomness?]