Обсуждение: Updating an emty table?

Hi..

I have a small "problem" and I was wondering if you guys could give me a
recommendation. I have a table that initially is empty but the table is
always updated, so there is no insert first then update..
The table can hold 10-20 records for each order, and I was wondering how
to implement this update. There are two alternatives as far as I can
see..:

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...


Regards,

BTJ


-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
btj@havleik.no                  Havleik Consulting
Phone : +47 67 54 15 17         Conradisvei 4
Fax : +47 67 54 13 91           N-1338 Sandvika
Cellular : +47 926 93 298       http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------

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

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