Hello 2010/10/31 Dmitriy Igrishin <dmitigr@xxxxxxxxx>: > Hey Alexander, Pavel > > The solution like below should works IMO, but it does not. > Â insert into pref_users(id, first_name, last_name, > Â Â female, avatar, city, last_ip) > ÂÂÂ select $1, $2, $3, $4, $5, $6, $7 > ÂÂÂÂÂ where not exists > ÂÂÂÂÂÂÂÂ (update pref_users set first_name = $2, > ÂÂ Â Â Â Â Â Â Â ÂÂ last_name = $3, > ÂÂ Â Â Â Â Â Â Â ÂÂ female = $4, > ÂÂ Â Â Â Â Â Â Â ÂÂ avatar = $5, > ÂÂ Â Â Â Â Â Â Â ÂÂ city = $6, > ÂÂ Â Â Â Â Â Â Â ÂÂ last_ip = $7 > ÂÂ Â Â Â Â Â ÂÂ where id = $1 > ÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂ returning id); > > BTW, I don't understand why it not possible to write query like this: > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS > foo; > According to the doc (of UPDATE command) "The syntax of the RETURNING list > is identical to > that of the output list of SELECT). > With this syntax, the OPs goal can be implemented in SQL.. > UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE RETURNING) directly. It's possible with wrapping to sql function. In next pg version 9.1 you can do it via Updatable Common Table Expression, but it isn't possible in older version. Regards Pavel Stehule > -- > // Dmitriy. > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
