Re: Kind of error-handler in a pgsql function
От | Alex Pilosov |
---|---|
Тема | Re: Kind of error-handler in a pgsql function |
Дата | |
Msg-id | Pine.BSO.4.10.10107051025570.7004-100000@spider.pilosoft.com обсуждение исходный текст |
Ответ на | Re: Kind of error-handler in a pgsql function (DI Hasenöhrl <i.hasenoehrl@aon.at>) |
Список | pgsql-sql |
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 stepsof 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 > >