Thread: User defined exceptions

User defined exceptions

From

Alex Ignatov

Date:

15 July 2015, 17:10:25

Hello all!<br /> Trying to emulate "named" user defined exception with:<br /> CREATE OR REPLACE FUNCTION exception_aaa
() RETURNS text AS $body$<br /> BEGIN<br />    return 31234;      <br /> END;<br /> $body$<br /> LANGUAGE PLPGSQL<br />
SECURITYDEFINER<br /> ;<br /><br /> do $$<br /> begin<br />    raise exception using errcode=exception_aaa();<br />
exception<br />    when  sqlstate exception_aaa()<br />    then<br />       raise notice 'got exception %',sqlstate;<br
/>end;<br /> $$<br />  <br /> Got:<br /><br /> ERROR:  syntax error at or near "exception_aaa"<br /> LINE 20: sqlstate
exception_aaa()<br/><br /> I looks like "when  sqlstate exception_aaa()" doesn't work. <br /><br /> How can I catch
exceptionin this case?<br /><pre class="moz-signature" cols="72">-- 
 
Alex Ignatov
Postgres Professional: <a class="moz-txt-link-freetext"
href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company

</pre><br /><br /><hr style="border:none; color:#909090; background-color:#B0B0B0; height: 1px; width: 99%;" /><table
style="border-collapse:collapse;border:none;"><tr><tdstyle="border:none;padding:0px 15px 0px 8px"><a
href="https://www.avast.com/antivirus"><img alt="Avast logo" border="0"
src="http://static.avast.com/emails/avast-mail-stamp.png"/> </a></td><td><p style="color:#3d4d5a;
font-family:"Calibri","Verdana","Arial","Helvetica";font-size:12pt;"> This email has been checked for viruses by Avast
antivirussoftware. <br /><a href="https://www.avast.com/antivirus">www.avast.com</a></td></tr></table><br />

Re: User defined exceptions

From

"David G. Johnston"

Date:

15 July 2015, 17:22:18

On Wed, Jul 15, 2015 at 10:10 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

Hello all!
Trying to emulate "named" user defined exception with:
CREATE OR REPLACE FUNCTION exception_aaa () RETURNS text AS $body$
BEGIN
   return 31234;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

do $$
begin
   raise exception using errcode=exception_aaa();
exception
   when sqlstate exception_aaa()
   then
      raise notice 'got exception %',sqlstate;
end;
$$

Got:

ERROR: syntax error at or near "exception_aaa"
LINE 20: sqlstate exception_aaa()

I looks like "when sqlstate exception_aaa()" doesn't work.

How can I catch exception in this case?

I'm doubtful that it can be done presently.

If it were possible your exception_aaa function would have to be declared IMMUTABLE. It also seems pointless to declare it security definer.

There is nothing in the documentation that suggests that (or, to be fair, prohibits) the "condition" can be anything other than a pre-defined name or a constant string. When plpgsql get a function body it doesn't go looking for random functions to execute.

David J.

Re: User defined exceptions

From

Pavel Stehule

Date:

15 July 2015, 18:55:37

2015-07-15 16:10 GMT+02:00 Alex Ignatov <a.ignatov@postgrespro.ru>:

Hello all!
Trying to emulate "named" user defined exception with:
CREATE OR REPLACE FUNCTION exception_aaa () RETURNS text AS $body$
BEGIN
   return 31234;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

do $$
begin
   raise exception using errcode=exception_aaa();
exception
   when sqlstate exception_aaa()
   then
      raise notice 'got exception %',sqlstate;
end;
$$

Got:

ERROR: syntax error at or near "exception_aaa"
LINE 20: sqlstate exception_aaa()

I looks like "when sqlstate exception_aaa()" doesn't work.

How can I catch exception in this case?

this syntax is working only for builtin exceptions. PostgreSQL has not declared custom exceptions like SQL/PSM.

You have to use own sqlcode and catch specific code.

Regards

Pavel

-- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

This email has been checked for viruses by Avast antivirus software.
www.avast.com

Re: User defined exceptions

From

Alexey Bashtanov

Date:

17 July 2015, 10:37:01

On 15.07.2015 17:10, Alex Ignatov wrote:
> Hello all!
> Trying to emulate "named" user defined exception with:
> CREATE OR REPLACE FUNCTION exception_aaa ()  RETURNS text AS $body$
> BEGIN
>    return 31234;
> END;
> $body$
> LANGUAGE PLPGSQL
> SECURITY DEFINER
> ;
>
> do $$
> begin
>    raise exception using errcode=exception_aaa();
> exception
>    when  sqlstate exception_aaa()
>    then
>       raise notice 'got exception %',sqlstate;
> end;
> $$
>
> Got:
>
> ERROR:  syntax error at or near "exception_aaa"
> LINE 20: sqlstate exception_aaa()
>
> I looks like "when  sqlstate exception_aaa()" doesn't work.
>
> How can I catch exception in this case?

Hello Alex,

The following workaround could be used:

do $$
begin   raise exception using errcode = exception_aaa();
exception   when others then      if sqlstate = exception_aaa() then         raise notice 'got exception %',sqlstate;
  else         raise; --reraise      end if;
 
end;
$$

Not sure if its performance is the same as in simple exception catch, 
maybe it would degrade.

Best Regards,   Alexey Bashtanov

Re: User defined exceptions

From

Alex Ignatov

Date:

17 July 2015, 18:31:46

<br /><br /><div class="moz-cite-prefix">On 17.07.2015 10:34, Alexey Bashtanov wrote:<br /></div><blockquote
cite="mid:55A8AFF3.7040305@ocslab.com"type="cite"><div class="moz-cite-prefix">On 15.07.2015 17:10, Alex Ignatov
wrote:<br/></div><blockquote cite="mid:%3C55A669CA.3070302@postgrespro.ru%3E" type="cite"> Hello all!<br /> Trying to
emulate"named" user defined exception with:<br /> CREATE OR REPLACE FUNCTION exception_aaa ()  RETURNS text AS
$body$<br/> BEGIN<br />    return 31234;      <br /> END;<br /> $body$<br /> LANGUAGE PLPGSQL<br /> SECURITY DEFINER<br
/>;<br /><br /> do $$<br /> begin<br />    raise exception using errcode=exception_aaa();<br /> exception <br />   
when sqlstate exception_aaa()<br />    then<br />       raise notice 'got exception %',sqlstate;<br /> end;<br /> $$<br
/> <br /> Got:<br /><br /> ERROR:  syntax error at or near "exception_aaa"<br /> LINE 20: sqlstate exception_aaa()<br
/><br/> I looks like "when  sqlstate exception_aaa()" doesn't work. <br /><br /> How can I catch exception in this
case?<br/></blockquote><br /> Hello Alex,<br /><br /> The following workaround could be used:<br /><br /> do $$<br />
begin<br/>    raise exception using errcode = exception_aaa();<br /> exception<br />    when others then<br />       if
sqlstate= exception_aaa() then<br />          raise notice 'got exception %',sqlstate;<br />       else<br />         
raise;--reraise<br />       end if;<br /> end;<br /> $$<br /><br /> Not sure if its performance is the same as in
simpleexception catch, maybe it would degrade.<br /><br /> Best Regards,<br />    Alexey Bashtanov<br /></blockquote>
Yepalready used this trick =)<br /> Anyway thank you!<br /><pre class="moz-signature" cols="72">-- 
 
Alex Ignatov
Postgres Professional: <a class="moz-txt-link-freetext"
href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company

</pre><br /><br /><hr style="border:none; color:#909090; background-color:#B0B0B0; height: 1px; width: 99%;" /><table
style="border-collapse:collapse;border:none;"><tr><tdstyle="border:none;padding:0px 15px 0px 8px"><a
href="https://www.avast.com/antivirus"><img alt="Avast logo" border="0"
src="http://static.avast.com/emails/avast-mail-stamp.png"/> </a></td><td><p style="color:#3d4d5a;
font-family:"Calibri","Verdana","Arial","Helvetica";font-size:12pt;"> This email has been checked for viruses by Avast
antivirussoftware. <br /><a href="https://www.avast.com/antivirus">www.avast.com</a></td></tr></table><br />

Re: User defined exceptions

From

Alexey Bashtanov

Date:

18 July 2015, 01:52:16

<div class="moz-cite-prefix">On 15.07.2015 17:10, Alex Ignatov wrote:<br /></div><blockquote
cite="mid:%3C55A669CA.3070302@postgrespro.ru%3E"type="cite"> Hello all!<br /> Trying to emulate "named" user defined
exceptionwith:<br /> CREATE OR REPLACE FUNCTION exception_aaa ()  RETURNS text AS $body$<br /> BEGIN<br />    return
31234;     <br /> END;<br /> $body$<br /> LANGUAGE PLPGSQL<br /> SECURITY DEFINER<br /> ;<br /><br /> do $$<br />
begin<br/>    raise exception using errcode=exception_aaa();<br /> exception <br />    when  sqlstate
exception_aaa()<br/>    then<br />       raise notice 'got exception %',sqlstate;<br /> end;<br /> $$<br />  <br />
Got:<br/><br /> ERROR:  syntax error at or near "exception_aaa"<br /> LINE 20: sqlstate exception_aaa()<br /><br /> I
lookslike "when  sqlstate exception_aaa()" doesn't work. <br /><br /> How can I catch exception in this case?<br
/></blockquote><br/> Hello Alex,<br /><br /> The following workaround could be used:<br /><br /> do $$<br /> begin<br
/>   raise exception using errcode = exception_aaa();<br /> exception<br />    when others then<br />       if sqlstate
=exception_aaa() then<br />          raise notice 'got exception %',sqlstate;<br />       else<br />          raise;
--reraise<br/>       end if;<br /> end;<br /> $$<br /><br /> Not sure if its performance is the same as in simple
exceptioncatch, maybe it would degrade.<br /><br /> Best Regards,<br />    Alexey Bashtanov<br />