Skip site navigation (1) Skip section navigation (2)

Re: Implementing replace function

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 (view raw or flat)
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.
>
>
>

In response to

pgsql-general by date

Next:From: Thomas KellererDate: 2010-10-31 10:07:51
Subject: Re: Implementing replace function
Previous:From: Dmitriy IgrishinDate: 2010-10-31 09:48:24
Subject: Re: Implementing replace function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group