agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

User defined exceptions
6+ messages / 5 participants
[nested] [flat]

* User defined exceptions
@ 2015-07-15 14:10  Alex Ignatov <a.ignatov@postgrespro.ru>
  0 siblings, 4 replies; 6+ messages in thread

From: Alex Ignatov @ 2015-07-15 14:10 UTC (permalink / raw)
  To: pgsql-sql

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?

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: User defined exceptions
@ 2015-07-15 14:22  David G. Johnston <david.g.johnston@gmail.com>
  parent: Alex Ignatov <a.ignatov@postgrespro.ru>
  3 siblings, 0 replies; 6+ messages in thread

From: David G. Johnston @ 2015-07-15 14:22 UTC (permalink / raw)
  To: Alex Ignatov <a.ignatov@postgrespro.ru>; +Cc: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: User defined exceptions
@ 2015-07-15 15:54  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Alex Ignatov <a.ignatov@postgrespro.ru>
  3 siblings, 0 replies; 6+ messages in thread

From: Pavel Stehule @ 2015-07-15 15:54 UTC (permalink / raw)
  To: Alex Ignatov <a.ignatov@postgrespro.ru>; +Cc: pgsql-sql

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
>
>
>
>
> ------------------------------
>   [image: Avast logo] <https://www.avast.com/antivirus;
>
> This email has been checked for viruses by Avast antivirus software.
> www.avast.com <https://www.avast.com/antivirus;
>
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: User defined exceptions
@ 2015-07-17 07:34  Alexey Bashtanov <alexey_bashtanov@ocslab.com>
  parent: Alex Ignatov <a.ignatov@postgrespro.ru>
  3 siblings, 1 reply; 6+ messages in thread

From: Alexey Bashtanov @ 2015-07-17 07:34 UTC (permalink / raw)
  To: Alex Ignatov <a.ignatov@postgrespro.ru>; pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: User defined exceptions
@ 2015-07-17 07:36  Alexey Bashtanov <bashtanov@imap.cc>
  parent: Alex Ignatov <a.ignatov@postgrespro.ru>
  3 siblings, 0 replies; 6+ messages in thread

From: Alexey Bashtanov @ 2015-07-17 07:36 UTC (permalink / raw)
  To: Alex Ignatov <a.ignatov@postgrespro.ru>; pgsql-sql

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: User defined exceptions
@ 2015-07-17 15:31  Alex Ignatov <a.ignatov@postgrespro.ru>
  parent: Alexey Bashtanov <alexey_bashtanov@ocslab.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Alex Ignatov @ 2015-07-17 15:31 UTC (permalink / raw)
  To: pgsql-sql



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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

end of thread, other threads:[~2015-07-17 15:31 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-15 14:10 User defined exceptions Alex Ignatov <a.ignatov@postgrespro.ru>
2015-07-15 14:22 ` David G. Johnston <david.g.johnston@gmail.com>
2015-07-15 15:54 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-07-17 07:34 ` Alexey Bashtanov <alexey_bashtanov@ocslab.com>
2015-07-17 15:31   ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-07-17 07:36 ` Alexey Bashtanov <bashtanov@imap.cc>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox