Implementing replace function

Hello

2010/10/31 Alexander Farber <alexander.farber@gmail.com>:

Hello Postgres users,

to mimic the MySQL-REPLACE statement I need
to try to UPDATE a record and if that fails - INSERT it.

But how can I detect that the UPDATE has failed in my SQL procedure?

see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
near to end of page

Regards

Pavel Stehule

Thanks Pavel, but I have an SQL procedure and not plpgsql?

2010/10/31 Alexander Farber <alexander.farber@gmail.com>:

Thanks Pavel, but I have an SQL procedure and not plpgsql?

it's not possible in sql. But plpgsql is same level like stored
procedures language from mysql.

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

--
// Dmitriy.

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

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

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

Alexander Farber wrote on 31.10.2010 09:22:

Hello Postgres users,

to mimic the MySQL-REPLACE statement I need
to try to UPDATE a record and if that fails - INSERT it.

There is actually an example of this in the PG manual ;)

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Here is another solution based on triggers:
http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html

Regards
Thomas

Thanks for all the comments.

Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are
functions atomic?

Regards
Alex

And would a pure SQL-function solution to call
an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore it
best though, so that I don't ignore other critical errors)

2010/10/31 Alexander Farber <alexander.farber@gmail.com>:

Thanks for all the comments.

Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are
functions atomic?

If you use a code from documentation, then you don't need explicit
transaction - every SQL run inside outer implicit transaction in
PostgreSQL.

Pavel

[corrected typo, sorry]

And wouldn't a pure SQL-function solution be:
to call an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore that error
best though, so that I don't ignore other critical errors)

2010/10/31 Alexander Farber <alexander.farber@gmail.com>:

And would a pure SQL-function solution to call
an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore it
best though, so that I don't ignore other critical errors)

You must not ignore errors in SQL - it's not T-SQL :)

Pavel

I've created a function now (below) and can call it as well,
but how can I see it at the psql prompt? Is there a \d command
for that or should I dump the database to see my declarations?

And is my function atomic? I.e. can't it happen, that FOUND
is not true, but then another session calls a INSERT inbetween?

pref=> create or replace function pref_update_users(_id varchar,
pref(> _first_name varchar, _last_name varchar, _female boolean,
pref(> _avatar varchar, _city varchar, _last_ip inet)
returns void as $BODY$
pref$> BEGIN
pref$>
pref$> update pref_users set
pref$> first_name = _first_name,
pref$> last_name = _last_name,
pref$> female = _female,
pref$> avatar = _avatar,
pref$> city = _city,
pref$> last_ip = _last_ip
pref$> where id = _id;
pref$>
pref$> IF NOT FOUND THEN
pref$> insert into pref_users(id, first_name,
pref$> last_name, female, avatar, city, last_ip)
pref$> values (_id, _first_name, _last_name,
pref$> _female, _avatar, _city, _last_ip);
pref$> END IF;
pref$> END;
pref$> $BODY$ language plpgsql;
CREATE FUNCTION

Thanks and merry Halloween
Alex

\df *update*

Alexander Farber wrote:

On 31/10/2010 17:28, Alexander Farber wrote:

I've created a function now (below) and can call it as well,
but how can I see it at the psql prompt? Is there a \d command
for that or should I dump the database to see my declarations?

You can do \df public.*, assuming that your function is in the public
schema, to get a list of all functions; and \df+ <function name> will
give you the function body also.

\? is your friend here.

And is my function atomic? I.e. can't it happen, that FOUND
is not true, but then another session calls a INSERT inbetween?

Yes - all functions are executed in an implicit transaction.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Hello,

If you expect your data to reach some kind of "critical size" at some
point ( ie updates will be more likely than inserts at that point ), you
can optimize your UPSERT code by trying to UPDATE before INSERTing.
Otherwise trying to INSERT first should decrease the average UPSERT
execution time in the long run, since you are less likely to hit the
exception and do some extra work on the table.

Anyway, glad to hear this is going to be accounted for in 9.1 . :)

Le 31/10/2010 11:07, Thomas Kellerer a �crit :

On Tue, Nov 02, 2010 at 10:46:42AM +0100, Matthieu Huin wrote:

Hello,

If you expect your data to reach some kind of "critical size" at some
point ( ie updates will be more likely than inserts at that point ), you
can optimize your UPSERT code by trying to UPDATE before INSERTing.
Otherwise trying to INSERT first should decrease the average UPSERT
execution time in the long run, since you are less likely to hit the
exception and do some extra work on the table.

You'd almost think of using some kind of branch prediction techniques.
You could track what happened the last two times and use that to
predict which would be better. There's always pathelogical cases, but
it could work well for normal workloads.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/