From: | Dmitriy Igrishin <dmitigr(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(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 09:48:24 |
Message-ID: | AANLkTimmxyD-FesnVZaVe8w7FvjtKbLpxgVjmLCLrg-U@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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 | Pavel Stehule | 2010-10-31 10:00:02 | Re: Implementing replace function |
Previous Message | Pavel Stehule | 2010-10-31 09:44:20 | Re: Implementing replace function |