Updating an emty table?

On Wed, 25 Jun 2003 10:40:16 +0200 (CEST)
"Bjorn T Johansen" <btj@havleik.no> wrote:

have never written any stored proc for PostgreSQL yet, I need some
pointers on how to implement this...

let's imagine:

create table some_test (codename text, counter int4);

# create or replace function addTest (text) returns int4 as '
depesz'# declare
depesz'# in_codename alias for $1;
depesz'# reply int4;
depesz'# begin
depesz'# select counter into reply from some_test where codename = in_codename;
depesz'# if found then
depesz'# update some_test set counter = counter + 1 where codename = in_codename;
depesz'# return reply + 1;
depesz'# end if;
depesz'# insert into some_test (codename, counter) values (in_codename, 1);
depesz'# return 1;
depesz'# end;
depesz'# ' language 'plpgsql';
CREATE FUNCTION
# select addtest('depesz');
addtest
---------
1
(1 row)

# select addtest('depesz');
addtest
---------
2
(1 row)

# select addtest('depesz');
addtest
---------
3
(1 row)

# select addtest('depeszx');
addtest
---------
1
(1 row)

hope this helps.

depesz

That helps... :)

I am guessing that since you are using an if statement, that PostgreSQL
doesn't support writing this using exceptions. (I know I have written
functions like this, trying to do an update first and if specific
exception occured I then do an insert...)

BTJ

On Wed, 25 Jun 2003 11:00:55 +0200 (CEST)
"Bjorn T Johansen" <btj@havleik.no> wrote:

I am guessing that since you are using an if statement, that PostgreSQL
doesn't support writing this using exceptions. (I know I have written
functions like this, trying to do an update first and if specific
exception occured I then do an insert...)

hmm .. exception will issue "rollback" thus making your transaction void. any update's or inserts would vanish.

depesz

On Wednesday 25 Jun 2003 9:40 am, Bjorn T Johansen wrote:
[snip]

1. I can delete every row for the order on every update and do an insert
instead.

2. or I can write a function (stored proc) that first tries to update
and if update fails, it then does an insert.

The first one I know how to do (don't know how much penalty this would
cause?) and the second one is perhaps the best way to do it, but since I
have never written any stored proc for PostgreSQL yet, I need some
pointers on how to implement this...

Someone else has an example showing a test/update combo, but one thing you
should be aware of is that with PG an update is effectively a delete+insert
anyway. This is part of the MVCC system so other transactions can still see
the old values while you're going about doing your updates.

I'd just wrap the delete/insert in a transaction and get back to coding my
app.

--
Richard Huxton