Re: Implementing replace function

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