Thread: how to check SQLSTATE
Hi,
is it possible to check for sqlstate inside a function , something like:
....
loop
fetch bla.....
if sqlstate = '02000' then
exit;
end if;
....
end loop;
if I try to save the above I get a : sqlstate not defined error
Then I tried this with no success:
....
loop
Begin
fetch bla.....
Exception
when no_data then
exit;
end;
....
end loop;
when I tried to save the trigger I got this: unrecognized exception condition "no_data"
but according to the help docs, no_data is a valid symbol.
could anybody give me some hints
thanks
Hugo
is it possible to check for sqlstate inside a function , something like:
....
loop
fetch bla.....
if sqlstate = '02000' then
exit;
end if;
....
end loop;
if I try to save the above I get a : sqlstate not defined error
Then I tried this with no success:
....
loop
Begin
fetch bla.....
Exception
when no_data then
exit;
end;
....
end loop;
when I tried to save the trigger I got this: unrecognized exception condition "no_data"
but according to the help docs, no_data is a valid symbol.
could anybody give me some hints
thanks
Hugo
am Thu, dem 05.10.2006, um 17:45:36 -0300 mailte Hugo folgendes: > Hi, > > is it possible to check for sqlstate inside a function , something like: > .... > loop > fetch bla..... > if sqlstate = '02000' then > exit; > end if; > .... > end loop; > if I try to save the above I get a : sqlstate not defined error Which version? With release 8.1 you can use SQLSTATE and SQLERRM inside exception blocks. -> http://www.postgresql.org/docs/8.1/interactive/release-8-1.html You can find the errorcodes there: http://www.pgadmin.org/docs/1.4/pg/errcodes-appendix.html#errcodes-table HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
sorry, forgot to mention psql8.1.4 on fedora core 4
On 10/6/06, A. Kretschmer <andreas.kretschmer@schollglas.com > wrote:
am Thu, dem 05.10.2006, um 17:45:36 -0300 mailte Hugo folgendes:
> Hi,
>
> is it possible to check for sqlstate inside a function , something like:
> ....
> loop
> fetch bla.....
> if sqlstate = '02000' then
> exit;
> end if;
> ....
> end loop;
> if I try to save the above I get a : sqlstate not defined error
Which version?
With release 8.1 you can use SQLSTATE and SQLERRM inside exception
blocks.
-> http://www.postgresql.org/docs/8.1/interactive/release-8-1.html
You can find the errorcodes there:
http://www.pgadmin.org/docs/1.4/pg/errcodes-appendix.html#errcodes-table
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Hi again, thanks for your guidance, this is the error I got trying to save my fuction:
ERROR: unrecognized exception condition "no_data"
CONTEXT: compile of PL/pgSQL function "fn_verificar_aportes_socio" near line 36
the symbol is correct , i checked it in the appendix A postgres error codes and constants, have you got any idea what the problem could be.
....
loop
Begin
fetch bla.....
Exception
when no_data then
this is on suse 10, postgres 8.1.4
thanks in advance
Hugo
ERROR: unrecognized exception condition "no_data"
CONTEXT: compile of PL/pgSQL function "fn_verificar_aportes_socio" near line 36
the symbol is correct , i checked it in the appendix A postgres error codes and constants, have you got any idea what the problem could be.
....
loop
Begin
fetch bla.....
Exception
when no_data then
exit;
end;
....
end loop;
......end;
....
end loop;
this is on suse 10, postgres 8.1.4
thanks in advance
Hugo
Hugo <htakada@gmail.com> writes: > Hi again, thanks for your guidance, this is the error I got trying to save > my fuction: > ERROR: unrecognized exception condition "no_data" > CONTEXT: compile of PL/pgSQL function "fn_verificar_aportes_socio" near > line 36 NO_DATA isn't an error condition, only a warning, thus there is no case in which an exception block would trap it. So plpgsql doesn't bother to recognize it. regards, tom lane
Hi,
Is there a way to check how many records where affected by a delete or update sentence in a trigger function ??
thanks
Hugo
Is there a way to check how many records where affected by a delete or update sentence in a trigger function ??
thanks
Hugo
On 10/6/06, Hugo <htakada@gmail.com> wrote:
OK, thanks, I'm porting a lot of sybase storedprocedures, and there are lots of "if sqlstate.." statements that i need to translate to pgpsql, obviously I cannot use the SQLSTATE pseudo variable directly in my fuctions, according to the documentation i can be used in a exception block but couldn't find any examples, could you point me to some document or give some advice on how i could use sqlstate pseudo variable?
thanks again
HugoOn 10/6/06, Tom Lane < tgl@sss.pgh.pa.us> wrote:Hugo <htakada@gmail.com> writes:
> Hi again, thanks for your guidance, this is the error I got trying to save
> my fuction:
> ERROR: unrecognized exception condition "no_data"
> CONTEXT: compile of PL/pgSQL function "fn_verificar_aportes_socio" near
> line 36
NO_DATA isn't an error condition, only a warning, thus there is no case
in which an exception block would trap it. So plpgsql doesn't bother to
recognize it.
regards, tom lane