Okay, Pavel, will wait for 9.1 :-) It is a common case - insert new row if it cannot be updated.
2010/10/31 Pavel Stehule <pavel.steh...@gmail.com> > Hello > > 2010/10/31 Dmitriy Igrishin <dmit...@gmail.com>: > > 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. > > > > > > > -- // Dmitriy.
