Message-ID: <55A91FDB.1010706@postgrespro.ru>
Date: Fri, 17 Jul 2015 18:31:39 +0300
From: Alex Ignatov <a.ignatov@postgrespro.ru>
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: User defined exceptions
References: <55A669CA.3070302@postgrespro.ru> <55A8AFF3.7040305@ocslab.com>
In-Reply-To: <55A8AFF3.7040305@ocslab.com>
Content-Type: multipart/alternative;
 boundary="------------080103000301010107000508"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------080103000301010107000508
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit


On 17.07.2015 10:34, Alexey Bashtanov wrote:
> 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
Yep already used this trick =)
Anyway thank you!

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


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

--------------080103000301010107000508
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <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">
      <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
      <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">
        <meta http-equiv="content-type" content="text/html;
          charset=utf-8">
        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>
        <meta http-equiv="content-type" content="text/html;
          charset=utf-8">
        <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 simple exception
      catch, maybe it would degrade.<br>
      <br>
      Best Regards,<br>
         Alexey Bashtanov<br>
    </blockquote>
    Yep already 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>
		<td style='border:none;padding:0px 15px 0px 8px'>
			<a href="https://www.avast.com/antivirus">
				<img border=0 src="http://static.avast.com/emails/avast-mail-stamp.png" alt="Avast logo" />
			</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 antivirus software.
				<br><a href="https://www.avast.com/antivirus">www.avast.com</a>
			</p>
		</td>
	</tr>
</table>
<br />
</body>
</html>

--------------080103000301010107000508--