Re: 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

>        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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>