From i.hasenoehrl@aon.at Wed Jul 4 19:37:00 2001 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 f64Jb0a34987 for ; Wed, 4 Jul 2001 15:37:00 -0400 (EDT) (envelope-from i.hasenoehrl@aon.at) Received: (qmail 62406 invoked from network); 4 Jul 2001 19:36:57 -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 ; 4 Jul 2001 19:36:57 -0000 Message-ID: <00c401c104c0$b9a03200$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: Subject: Kind of error-handler in a pgsql function Date: Wed, 4 Jul 2001 21:37:13 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C1_01C104D1.7CE5DE80" 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/40 X-Sequence-Number: 3821 This is a multi-part message in MIME format. ------=_NextPart_000_00C1_01C104D1.7CE5DE80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, 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 Is there a possibility to make a difference in a pgsql function like this: 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'; 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'; I want to know the result of an insert or update, because I call these func= tions from an Access form and the next steps of the program depend on these= results. I hope, someone can help me, because I didn't find anything in the docu or = mailing list. Thanks in advance Irina E-Mail: i.hasenoehrl@aon.at ------=_NextPart_000_00C1_01C104D1.7CE5DE80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
When I write in psql:
testdb=3D# update table1 set tableattribut= e=3D'any'=20 where table_nr=3D1;
    if a tuple exists, I ge= t this=20 message
testdb=3D# update 1
    if no tuple with table_= nr=3D1=20 exists, I get this message
testdb=3D# update 0
   
 
Is there a possibility to make a differenc= e in a=20 pgsql function like this:
create function updTable(text,integer) ret= urns int=20 AS
'DECLARE
   msg ALIAS FOR $1;
   nr    ALI= AS FOR=20 $2;
 BEGIN
     update table1 set= =20 tableattribute=3Dmsg where table_nr=3Dnr;
    =20 --pseudocode
       if update =3D 0=20 then
         return=20 0;
     &nbs= p;=20 else
         return=20 1;
     &nbs= p; end=20 if;
   END;
'language 'plpgsql';
 
or for a function, which inserts data:
create function insTable(text,integer) ret= urns int=20 AS
'DECLARE
   msg ALIAS FOR $1;
   nr    ALI= AS FOR=20 $2;
 BEGIN
     insert into table= 1 values=20 (nr,msg);
    =20 --pseudocode
       if error=3D cannot inser= t=20 duplicate key.....then
         return=20 0;
     &nbs= p;=20 else
         return=20 1;
     &nbs= p; end=20 if;
   END;
'language 'plpgsql';
 
I want to know the result of an insert or update, because I call these= =20 functions from an Access form and the next steps of the program depend= on=20 these results.
 
I hope, someone can help me, because I didn't find anything in the doc= u or=20 mailing list.
Thanks in advance
Irina
 
E-Mail: i.hasenoehrl@aon.at
------=_NextPart_000_00C1_01C104D1.7CE5DE80-- From tgl@sss.pgh.pa.us Thu Jul 5 00:38:54 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f650csa23128 for ; Wed, 4 Jul 2001 20:38:54 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f650cPJ01520; Wed, 4 Jul 2001 20:38:25 -0400 (EDT) To: =?iso-8859-1?Q?DI_Hasen=F6hrl?= cc: pgsql-sql@postgresql.org Subject: Re: Kind of error-handler in a pgsql function In-reply-to: <00c401c104c0$b9a03200$01011eac@irina> References: <00c401c104c0$b9a03200$01011eac@irina> Comments: In-reply-to =?iso-8859-1?Q?DI_Hasen=F6hrl?= message dated "Wed, 04 Jul 2001 21:37:13 +0200" Date: Wed, 04 Jul 2001 20:38:25 -0400 Message-ID: <1517.994293505@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/41 X-Sequence-Number: 3822 =?iso-8859-1?Q?DI_Hasen=F6hrl?= writes: > I want to know the result of an insert or update, because I call these func= > tions from an Access form and the next steps of the program depend on these= > results. See the FOUND variable (if you just want to test zero or not-zero rows processed) or the SET DIAGNOSTICS statement to get the row count. There is no way to trap an error inside a plpgsql function, however. regards, tom lane From alex@pilosoft.com Thu Jul 5 03:01:26 2001 Received: from spider.pilosoft.com (p55-222.acedsl.com [160.79.55.222]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6531Qa62376 for ; Wed, 4 Jul 2001 23:01:26 -0400 (EDT) (envelope-from alex@pilosoft.com) Received: from localhost (alexmail@localhost) by spider.pilosoft.com (8.9.3/8.9.3) with ESMTP id XAA03419; Wed, 4 Jul 2001 23:12:04 -0400 (EDT) Date: Wed, 4 Jul 2001 23:12:03 -0400 (EDT) From: Alex Pilosov To: =?iso-8859-1?Q?DI_Hasen=F6hrl?= cc: pgsql-sql@postgresql.org Subject: Re: Kind of error-handler in a pgsql function In-Reply-To: <00c401c104c0$b9a03200$01011eac@irina> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id f6531Ra62378 X-Archive-Number: 200107/42 X-Sequence-Number: 3823 You must do it the other way around: 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. Sample code: create function ... ... declare rec_affected int; begin update ... get diagnostics rec_affected = ROW_COUNT; if rec_affected = 0 then insert ... end if; end; On Wed, 4 Jul 2001, [iso-8859-1] DI Hasenöhrl wrote: > Hi, > > When I write in psql: > testdb=# update table1 set tableattribute='any' where table_nr=1; > if a tuple exists, I get this message > testdb=# update 1 > if no tuple with table_nr=1 exists, I get this message > testdb=# update 0 > > > Is there a possibility to make a difference in a pgsql function like this: > create function updTable(text,integer) returns int AS > 'DECLARE > msg ALIAS FOR $1; > nr ALIAS FOR $2; > BEGIN > update table1 set tableattribute=msg where table_nr=nr; > --pseudocode > if update = 0 then > return 0; > else > return 1; > end if; > END; > 'language 'plpgsql'; > > 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= cannot insert duplicate key.....then > return 0; > else > return 1; > end if; > END; > 'language 'plpgsql'; > > 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 these results. > > I hope, someone can help me, because I didn't find anything in the docu or mailing list. > Thanks in advance > Irina > > E-Mail: i.hasenoehrl@aon.at > From i.hasenoehrl@aon.at Thu Jul 5 08:02:06 2001 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-- From alex@pilosoft.com Thu Jul 5 14:18:42 2001 Received: from spider.pilosoft.com (p55-222.acedsl.com [160.79.55.222]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f65EIga39500 for ; Thu, 5 Jul 2001 10:18:42 -0400 (EDT) (envelope-from alex@pilosoft.com) Received: from localhost (alexmail@localhost) by spider.pilosoft.com (8.9.3/8.9.3) with ESMTP id KAA05238; Thu, 5 Jul 2001 10:29:18 -0400 (EDT) Date: Thu, 5 Jul 2001 10:29:18 -0400 (EDT) From: Alex Pilosov To: =?iso-8859-1?Q?DI_Hasen=F6hrl?= cc: pgsql-sql@postgresql.org Subject: Re: Kind of error-handler in a pgsql function In-Reply-To: <002801c10528$cfe35f20$01011eac@irina> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id f65EIha39515 X-Archive-Number: 200107/46 X-Sequence-Number: 3827 There is _no error handling_ in plpgsql You can find documentation at: http://www.postgresql.org/idocs/index.php?plpgsql.html On Thu, 5 Jul 2001, [iso-8859-1] DI Hasenöhrl wrote: > 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/postgres/programmer.html, > but I didn't find > > Many thanks in advance > Irina > > You must do it the other way around: > > 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. > > Sample code: > > create function ... > ... > declare rec_affected int; > begin > update ... > get diagnostics rec_affected = ROW_COUNT; > if rec_affected = 0 then > insert ... > end if; > end; > > On Wed, 4 Jul 2001, [iso-8859-1] DI Hasenöhrl wrote: > > > Hi, > > > > When I write in psql: > > testdb=# update table1 set tableattribute='any' where table_nr=1; > > if a tuple exists, I get this message > > testdb=# update 1 > > if no tuple with table_nr=1 exists, I get this message > > testdb=# update 0 > > > > > > Is there a possibility to make a difference in a pgsql function like this: > > create function updTable(text,integer) returns int AS > > 'DECLARE > > msg ALIAS FOR $1; > > nr ALIAS FOR $2; > > BEGIN > > update table1 set tableattribute=msg where table_nr=nr; > > --pseudocode > > if update = 0 then > > return 0; > > else > > return 1; > > end if; > > END; > > 'language 'plpgsql'; > > > > 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= cannot insert duplicate key.....then > > return 0; > > else > > return 1; > > end if; > > END; > > 'language 'plpgsql'; > > > > 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 these results. > > > > I hope, someone can help me, because I didn't find anything in the docu or mailing list. > > Thanks in advance > > Irina > > > > E-Mail: i.hasenoehrl@aon.at > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >