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.stehule@gmail.com>Hello
2010/10/31 Dmitriy Igrishin <dmitigr@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..
>
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.
