Re: UPDATE...RETURNING col INTO var - what is returned?

Thank you for your reply.
Yes, testing proved "after" values being returned.

Nevertheless I'm wondering whether this behaviour is (formally) defined somewhere
or just happens to be so by implementation detail.
(Of course, this is a minor concern as the PG team for sure will guard against a change of the existing semantics....)

With that behaviour a second question rising is:

Is there an easy way to update columns of a table and depend on the previous values in an efficient way?
(Clearly, one could use SELECT RETURNING INTO before the update,
but that is for sure not my notion of "efficient")

This caused my initial doubts:
In most cases if you update a column value, you will "know" the new value (as you control the assignment).
(Exceptions might be using timestamp values (one that change in real time not such that stay with transaction start)
or using values written by other transactions (if transaction separation would allow; might not be save anyway))

So returning "old" values would have provided access to additional information, right?

Rainer

Am 27.05.2010 19:12, schrieb Alban Hertroys:
> On 27 May 2010, at 18:12, Rainer Pruy wrote:
>
>> Hi all,
>> I just got quite confused on the exact semantics
>> of "RETURNING expressions INTO target" with an update statement.
>>
>> And while trying to resolve failed to find an answer in the docs.
>>
>> Question was whether - in case "expression" is involving a column that is assigned by the update itself -
>> the value returned is based on the "old" value or the "new" value".
>>
>> While I'm no back to assuming is has to be the "old" one,
>> I did not find a reference to that fact with the docs.
>
> I'm pretty sure it's the new value, since that's what INSERT...RETURNING returns - very convenient if the value you inserted was generated somehow (by a sequence for example).
> Since you can also UPDATE some column using a generated value, it'd make sense if it would behave the same way.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:737,4bfea81210411699814628!
>
>
>

--
Rainer Pruy
Geschäftsführer

Acrys Consult GmbH & Co. KG
Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel
Tel: +49-6101-98760-0 Fax: +49-6101-98760-50
Web: http://www.acrys.com - Email: office(at)acrys(dot)com
Handelsregister: Frankfurt am Main, HRA 31151
Komplementärin: Acrys Verwaltungs GmbH
Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel
Handelsregister: Frankfurt am Main, HRB 57625