Received: from email03.aon.at (WARSL401PIP8.highway.telekom.at [195.3.96.117]) by postgresql.org (8.11.3/8.11.1) with SMTP id f65826a08686 for ; Thu, 5 Jul 2001 04:02:06 -0400 (EDT) (envelope-from i.hasenoehrl@aon.at) Received: (qmail 157154 invoked from network); 5 Jul 2001 08:02:02 -0000 Received: from n681p022.adsl.highway.telekom.at (HELO irina) ([62.47.29.22]) (envelope-sender ) by qmail3.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 5 Jul 2001 08:02:02 -0000 Message-ID: <002801c10528$cfe35f20$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: "Alex Pilosov" Cc: References: Subject: Re: Kind of error-handler in a pgsql function Date: Thu, 5 Jul 2001 10:02:18 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0025_01C10539.9310A1A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200107/44 X-Sequence-Number: 3825 This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C10539.9310A1A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thank you for your example. I understand, in order to avoid inserting *duplicate keys*, first I try to = update, when it fails make an insert. But other errors may occur, is there = a possibility to distinguish between errors by an error-number. Please can you tell me, where I can find system functions, variables like *= diagnostics, row_count, found, and so on.....* I studied the docu in http://www.ca.postgresql.org/devel-corner/docs/postgr= es/programmer.html, but I didn't find Many thanks in advance Irina You must do it the other way around:=20 First, try update, then see how many records were updated, if there were 0 records updated, then do the insert. Currently, plpgsql lacks decent exception handling.=20 Sample code: create function ... ... declare rec_affected int; begin update ...=20 get diagnostics rec_affected =3D ROW_COUNT; if rec_affected =3D 0 then=20=20 insert ... end if; end; On Wed, 4 Jul 2001, [iso-8859-1] DI Hasen=F6hrl wrote: > Hi, >=20 > When I write in psql: > testdb=3D# update table1 set tableattribute=3D'any' where table_nr=3D1; > if a tuple exists, I get this message=20 > testdb=3D# update 1 > if no tuple with table_nr=3D1 exists, I get this message > testdb=3D# update 0 >=20=20=20=20=20 >=20 > Is there a possibility to make a difference in a pgsql function like th= is: > create function updTable(text,integer) returns int AS > 'DECLARE > msg ALIAS FOR $1; > nr ALIAS FOR $2; > BEGIN > update table1 set tableattribute=3Dmsg where table_nr=3Dnr; > --pseudocode > if update =3D 0 then > return 0; > else > return 1; > end if; > END; > 'language 'plpgsql'; >=20 > or for a function, which inserts data: > create function insTable(text,integer) returns int AS > 'DECLARE > msg ALIAS FOR $1; > nr ALIAS FOR $2; > BEGIN > insert into table1 values (nr,msg); > --pseudocode > if error=3D cannot insert duplicate key.....then > return 0; > else > return 1; > end if; > END; > 'language 'plpgsql'; >=20 > I want to know the result of an insert or update, because I call these = functions from an Access form and the next steps of the program depend on t= hese results. >=20 > I hope, someone can help me, because I didn't find anything in the docu= or mailing list. > Thanks in advance > Irina >=20 > E-Mail: i.hasenoehrl@aon.at >=20 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org ------=_NextPart_000_0025_01C10539.9310A1A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thank you for your example.
I understand, in order to avoid inserting= =20 *duplicate keys*, first I try to update, when it fails make an insert. But = other=20 errors may occur, is there a possibility to distinguish between errors by a= n=20 error-number.
Please can you tell me, where I can find s= ystem=20 functions, variables like *diagnostics, row_count, found, and so=20 on.....*
I studied the docu in http://www.ca.postgresql.org/devel-corner/docs/postgres/programmer.ht= ml,=20 but I didn't find
 
Many thanks in advance
Irina
 
You must do it the other way around:

First, try= =20 update, then see how many records were updated, if there were 0
record= s=20 updated, then do the insert.

Currently, plpgsql lacks decent excep= tion=20 handling.

Sample code:

create function ...
...
decla= re=20 rec_affected int;
begin
  update ...
  get diagnostic= s=20 rec_affected =3D ROW_COUNT;
  if rec_affected =3D 0 then =20
    insert ...
  end if;
end;

On Wed= , 4=20 Jul 2001, [iso-8859-1] DI Hasen=F6hrl wrote:

> Hi,
>
= >=20 When I write in psql:
> testdb=3D# update table1 set tableattribute= =3D'any'=20 where table_nr=3D1;
>     if a tuple exists, I = get=20 this message
> testdb=3D# update 1
>    = if no=20 tuple with table_nr=3D1 exists, I get this message
> testdb=3D# upd= ate=20 0
>    
>
> Is there a possibilit= y to=20 make a difference in a pgsql function like this:
> create function= =20 updTable(text,integer) returns int AS
>=20 'DECLARE
>    msg ALIAS FOR $1;
>  &= nbsp;=20 nr    ALIAS FOR $2;
> =20 BEGIN
>      update table1 set=20 tableattribute=3Dmsg where table_nr=3Dnr;
>    =  =20 --pseudocode
>        if update = =3D 0=20 then
>          return= =20 0;
>       =20 else
>          return= =20 1;
>        end=20 if;
>    END;
> 'language 'plpgsql';
>= =20
> or for a function, which inserts data:
> create function= =20 insTable(text,integer) returns int AS
>=20 'DECLARE
>    msg ALIAS FOR $1;
>  &= nbsp;=20 nr    ALIAS FOR $2;
> =20 BEGIN
>      insert into table1 values=20 (nr,msg);
>     =20 --pseudocode
>        if error= =3D=20 cannot insert duplicate=20 key.....then
>         = ;=20 return 0;
>       =20 else
>          return= =20 1;
>        end=20 if;
>    END;
> 'language 'plpgsql';
>= =20
> I want to know the result of an insert or update, because I call= =20 these functions from an Access form and the next steps of the program dep= end=20 on these results.
>
> I hope, someone can help me, because I= =20 didn't find anything in the docu or mailing list.
> Thanks in=20 advance
> Irina
>
> E-Mail: i.hasenoehrl@aon.at
>=20


---------------------------(end of=20 broadcast)---------------------------
TIP 1: subscribe and unsubscribe= =20 commands go to majordomo@postgresql.org
= ------=_NextPart_000_0025_01C10539.9310A1A0--