Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Hi All,

In the following documentation it advises that the UPDATE statement should
be able to return a value into a variable in plpgsql.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html

It works for the INSERT command, but not UPDATE. For the INSERT command, it
makes my code look neater and I image it's more efficient too.

This time I am trying to UPDATE a field using a primary key, and return
another field into a variable so that I can take necessary action if
required later in the plpgsql script. I know that I can issue another
SELECT query to retrieve the information, but I would have thought it would
be a lot more efficient to return the value during the UPDATE.

Regards,

Dale.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, 20 August 2008 1:30
To: Alvaro Herrera
Cc: Bill Moran; Dale; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to
compile successfully

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Bill Moran wrote:
>> Is it possible that your UPDATE command is updating multiple rows? I
>> don't believe RETURNING will work on an UPDATE that touches more than 1
>> row.

> Hmm, why not?

plpgsql will reject UPDATE RETURNING INTO that returns more than one
row, since it hasn't got any place to put the additional data.

It wasn't clear to me whether the OP was even using plpgsql, but if he
was, the example should have worked. We'd need to see more detail to
guess what the real problem is.

regards, tom lane