Detect that an UPDATE has failed in PostgreSQL SQL-procedure (not PlPgSQL)

Question

Hello,

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?

Thank you, Alex

Answer 1

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

Answer 2

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.