From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Dmitriy Igrishin <dmitigr(at)gmail(dot)com> |
Cc: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Implementing replace function |
Date: | 2010-10-31 10:00:02 |
Message-ID: | AANLkTi=E-Hw8t5soLJ2c22OOrw_Sdb1Dd84QFOB3Sfvy@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2010/10/31 Dmitriy Igrishin <dmitigr(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>
>>
>> Hello
>>
>> 2010/10/31 Dmitriy Igrishin <dmitigr(at)gmail(dot)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.
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2010-10-31 10:07:51 | Re: Implementing replace function |
Previous Message | Dmitriy Igrishin | 2010-10-31 09:48:24 | Re: Implementing replace function |