Detect that an UPDATE has failed in PostgreSQL SQL-function (not PL/pgSQL)

Question

To mimic the MySQL-REPLACE statement (aka UPSERT) 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?

begin transaction;

create table pref_users (
       id varchar(32) primary key,
       first_name varchar(32),
       last_name varchar(32),
       female boolean,
       avatar varchar(128),
       city varchar(32),
       lat real check (-90 <= lat and lat <= 90),
       lng real check (-90 <= lng and lng <= 90),
       last_login timestamp default current_timestamp,
       last_ip inet,
       medals smallint check (medals > 0)
);
create table pref_rate (
       obj varchar(32) references pref_users(id),
       subj varchar(32) references pref_users(id),
       good boolean,
       fair boolean,
       nice boolean,
       about varchar(256),
       last_rated timestamp default current_timestamp
);

create table pref_money (
       id varchar(32) references pref_users,
       yw char(7) default to_char(current_timestamp, 'YYYY-WW'),
       money real
);
create index pref_money_yw_index on pref_money(yw);

create or replace function update_pref_users(id varchar,
       first_name varchar, last_name varchar, female boolean,
       avatar varchar, city varchar, last_ip inet) returns void as $$

       update pref_users set
            first_name = $2,
            last_name = $3,
            female = $4,
            avatar = $5,
            city = $6,
            last_ip = $7
        where id = $1;

        -- XXX how to detect failure here? XXX

       insert into pref_users(id, first_name, last_name,
            female, avatar, city, last_ip)
            values ($1, $2, $3, $4, $5, $6, $7);
$$ language sql;

commit;

And do I need a 2nd pair of BEGIN/COMMIT inside of my update_pref_users SQL-function?

I am not sure if I understood correcly. Plpgsql function is atomic in nature. If some thing fails it will be rollback. — user493227
– user493227, Commented Nov 1, 2010 at 5:28

Frank Heikens · Accepted Answer · 2010-10-31 11:44:23Z

13

You can't use SQL as a language, you need pl/pgsql because of the lack of if-else constructions in SQL.

Within pl/pgsql you can use the special variable FOUND to see if a query has found something.

UPDATE ...;
IF NOT FOUND THEN -- UPDATE didn't touch anything
  INSERT ...;
END IF;

There is no need for extra SELECT statements.

answered Oct 31, 2010 at 11:44

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander Farber Over a year ago

Thank you for the answer. I wonder if it is "atomic", i.e. can't it happen, that FOUND is not true, but then another session calls an INSERT inbetween?

Lavir the Whiolet · Accepted Answer · 2010-10-31 08:40:24Z

2

IF EXISTS(<query to select record required for update>)
    UPDATE ...
ELSE
    INSERT ...

answered Oct 31, 2010 at 8:40

Lavir the Whiolet

1,02610 silver badges18 bronze badges

2 Comments

Alexander Farber Over a year ago

Thanks, but isn't it plpgsql? I'm trying to find an SQL solution. And is your suggestion atomic or can it fail if there is an INSERT inbetween?

Frank Heikens Over a year ago

There is no need for the SELECT query, just use FOUND to see if the UPDATE has touched anything.

Collectives™ on Stack Overflow

Detect that an UPDATE has failed in PostgreSQL SQL-function (not PL/pgSQL)

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related