Re: Implementing replace function

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..
>

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.
>
>
>