From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Implementing replace function |
Date: | 2010-10-31 08:34:24 |
Message-ID: | AANLkTi=cD_cPSYpH=gdTCDTpHGE1rvOCPrasO9vho=77@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
2010/10/31 Alexander Farber <alexander(dot)farber(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | AI Rumman | 2010-10-31 08:43:59 | max_fsm_pages increase |
Previous Message | Alexander Farber | 2010-10-31 08:22:48 | Implementing replace function |