2010/10/31 Dmitriy Igrishin <dmitigr@xxxxxxxxx>: > Okay, Pavel, will wait for 9.1 :-) > > It is a common case - insert new row if it cannot be updated. you can find (probably) MERGE statement in 9.1. Pavel > > 2010/10/31 Pavel Stehule <pavel.stehule@xxxxxxxxx> >> >> 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. >> > >> > >> > > > > > -- > // Dmitriy. > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
