Postgres Pro
Downloads
Home   >   mailing lists

Thread: INSERT doc discrepancy

INSERT doc discrepancy

From
Kristo Kaiv
Date:
INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ AS output_name ] [, ...] ]

but it seems if i want to return the result into a record i have to use it with INTO clause in the end:

        INSERT INTO tablename (
             a
            ,b
            ,c
        ) VALUES (
             in_a
            ,in_b
            ,in_c
        ) RETURNING * INTO _r;

using either

        INSERT INTO tablename (
             a
            ,b
            ,c
        ) VALUES (
             in_a
            ,in_b
            ,in_c
        ) RETURNING  _r;

or 

        INSERT INTO tablename (
             a
            ,b
            ,c
        ) VALUES (
             in_a
            ,in_b
            ,in_c
        ) RETURNING * AS _r;

didn't work on PostgreSQL 8.2.4 

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)


Re: INSERT doc discrepancy

From
Alvaro Herrera
Date:
Kristo Kaiv wrote:
> INSERT INTO table [ ( column [, ...] ) ]
>     { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...]
> | query }
>     [ RETURNING * | output_expression [ AS output_name ] [, ...] ]
>
> but it seems if i want to return the result into a record i have to use it
> with INTO clause in the end:
>
>         INSERT INTO tablename (
>              a
>             ,b
>             ,c
>         ) VALUES (
>              in_a
>             ,in_b
>             ,in_c
>         ) RETURNING * INTO _r;

Where's the discrepancy?  INTO is not supported in the RETURNING clause.

... thinks for a while ...

Ah, you are using it in plpgsql!  OK, but the explanation to the
discrepancy is that the second INTO is not part of the SQL sentence;
it's plpgsql only, and is parsed by its internal parser, so not really
part of the SQL grammar.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: INSERT doc discrepancy

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Kristo Kaiv wrote:
>> but it seems if i want to return the result into a record i have to use it
>> with INTO clause in the end:

> Ah, you are using it in plpgsql!  OK, but the explanation to the
> discrepancy is that the second INTO is not part of the SQL sentence;
> it's plpgsql only, and is parsed by its internal parser, so not really
> part of the SQL grammar.

And, in fact, the plpgsql documentation does show this usage (section
38.5.3 in devel docs, but I think the numbering has changed since 8.2).

            regards, tom lane