User defined exceptions

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

On Wed, Jul 15, 2015 at 10:10 AM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)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.

2015-07-15 16:10 GMT+02:00 Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)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>
>
>

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

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

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