Implementing replace function

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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?

        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;

Thank you
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general