Обсуждение: FW: RETURNING in stored procedure

Thanks, this works :)

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Leif B.
Kristensen
Sent: Friday, May 22, 2009 2:46 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] RETURNING in stored procedure

On Friday 22. May 2009, Mehrotra, Abhinav (GE Healthcare) wrote:
>Val:= insert into temp("hello") RETURNING seq;

You may try:

INSERT INTO temp("hello") RETURNING seq INTO val;
--
Leif Biberg Kristensen | Registered Linux User #338009 Me And My
Database: http://solumslekt.org/blog/

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On 2009-05-22, Mehrotra, Abhinav (GE Healthcare) <Abhinav.Mehrotra@ge.com> wrote:
>
> Thanks, this works :)
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Leif B.
> Kristensen
> Sent: Friday, May 22, 2009 2:46 PM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] RETURNING in stored procedure
>
> On Friday 22. May 2009, Mehrotra, Abhinav (GE Healthcare) wrote:
>>Val:= insert into temp("hello") RETURNING seq;

postgres seems to translate anything after a := into a select,
(something I often exploit)

thus you can do

  var := expression FROM table_name WHERE condition_expression ;

etc.

but INSERT-RETURNING isn't treated like an expression, or like a
query so it doesn't work as above.


> INSERT INTO temp("hello") RETURNING seq INTO val;

yeah.

On Saturday 23. May 2009, Jasen Betts wrote:
>On 2009-05-22, Mehrotra, Abhinav (GE Healthcare)
<Abhinav.Mehrotra@ge.com> wrote:
>>>Val:= insert into temp("hello") RETURNING seq;
>
>postgres seems to translate anything after a := into a select,
>(something I often exploit)
>
>thus you can do
>
>  var := expression FROM table_name WHERE condition_expression ;
>
>etc.
>
>but INSERT-RETURNING isn't treated like an expression, or like a
>query so it doesn't work as above.
>
>> INSERT INTO temp("hello") RETURNING seq INTO val;
>
>yeah.

I still think that OP's syntax is the most intuitive one, and IMO it
should be possible to do variable assignments this way in plpgsql from
RETURNING values. It's the «normal» way of doing assignments in almost
every other computer language that I know of.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

On 2009-05-23, Leif B. Kristensen <leif@solumslekt.org> wrote:
> On Saturday 23. May 2009, Jasen Betts wrote:
>>On 2009-05-22, Mehrotra, Abhinav (GE Healthcare)
><Abhinav.Mehrotra@ge.com> wrote:
>>>>Val:= insert into temp("hello") RETURNING seq;
>>
>>postgres seems to translate anything after a := into a select,
>>(something I often exploit)
>>
>>thus you can do
>>
>>  var := expression FROM table_name WHERE condition_expression ;
>>
>>etc.
>>
>>but INSERT-RETURNING isn't treated like an expression, or like a
>>query so it doesn't work as above.
>>
>>> INSERT INTO temp("hello") RETURNING seq INTO val;
>>
>>yeah.
>
> I still think that OP's syntax is the most intuitive one, and IMO it
> should be possible to do variable assignments this way in plpgsql from
> RETURNING values. It's the «normal» way of doing assignments in almost
> every other computer language that I know of.

I agree, however Insert...returning can't be used as a subquery.
which is effectively what he was doing.

I'm not sure why it can't be used as a subquery.