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.10107042305140.7004-100000@spider.pilosoft.com обсуждение исходный текст |
Ответ на | Kind of error-handler in a pgsql function (DI Hasenöhrl <i.hasenoehrl@aon.at>) |
Список | pgsql-sql |
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 >
В списке pgsql-sql по дате отправления: