Re: Implementing replace function

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hello

2010/10/31 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?
>

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

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