Re: INSERT .... RETURNING

On Wed, Nov 5, 2008 at 2:41 AM, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
> 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)?

This is a (very) faq.
Everyone wants it.
It's more complicated than it looks (triggers).
8.4 partially addresses this...it works in functions.

merlin