Re: Implementing replace function

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

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