Re: Implementing replace function

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.