SEO

#1 (**permalink**) 04-09-2008, 11:42 AM

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

#2 (**permalink**) 04-09-2008, 11:42 AM

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/i...lease-8-1.html

You can find the errorcodes there:
http://www.pgadmin.org/docs/1.4/pg/e...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

#3 (**permalink**) 04-09-2008, 11:42 AM

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/i...lease-8-1.html
>
> You can find the errorcodes there:
> http://www.pgadmin.org/docs/1.4/pg/e...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
>

#4 (**permalink**) 04-09-2008, 11:43 AM

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
exit;
end;
....
end loop;
.......
this is on suse 10, postgres 8.1.4

thanks in advance

Hugo

#5 (**permalink**) 04-09-2008, 11:43 AM

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#6 (**permalink**) 04-09-2008, 12:01 PM

Hi,

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
>
> Hugo
>
> On 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
> >
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

how to check SQLSTATE