Re: Dyamic updates of NEW with pl/pgsql

Merlin Moncure írta:
> On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
>   
>> What's wrong with "UPDATE foo SET (foo) = (NEW);" ?
>>
>>     
>
> amen brother! :-)
>
> I say though, since you can do:
> SELECT foo FROM foo;
> why not
> UPDATE foo SET foo = new;?
>   

I just tried this:

zozo=# create table foo (foo integer, bar integer);
CREATE TABLE
zozo=# insert into foo values (1, 2), (2, 4);
INSERT 0 2
zozo=# select foo from foo;foo
-----  1  2
(2 rows)

zozo=# create table foo1 (foo integer, bar integer);
CREATE TABLE
zozo=# insert into foo1 values (1, 2), (2, 4);
INSERT 0 2
zozo=# select foo1 from foo1;foo1 
-------(1,2)(2,4)
(2 rows)

So, if the table has field that's name is the same as the table name
then SELECT foo FROM foo; returns the field, not the whole row,
it's some kind of a precedence handling. What we could do is the
reverse precedence with   UPDATE foo SET foo = 3 WHERE foo = 1;
vs   UPDATE foo SET (foo) = (1,3) WHERE (foo) = (1,2);

Note the WHERE condition, I would expect it to work there, too.
If it works in plain SQL then no special casing would be needed
in PLs.

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/