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