agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUser defined exceptions
6+ messages / 5 participants
[nested] [flat]
* User defined exceptions
@ 2015-07-15 14:10 Alex Ignatov <a.ignatov@postgrespro.ru>
2015-07-15 14:22 ` Re: User defined exceptions David G. Johnston <david.g.johnston@gmail.com>
2015-07-15 15:54 ` Re: User defined exceptions Pavel Stehule <pavel.stehule@gmail.com>
2015-07-17 07:34 ` Re: User defined exceptions Alexey Bashtanov <alexey_bashtanov@ocslab.com>
2015-07-17 07:36 ` Re: User defined exceptions Alexey Bashtanov <bashtanov@imap.cc>
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:10 User defined exceptions Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-07-15 14:22 ` David G. Johnston <david.g.johnston@gmail.com>
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 14:10 User defined exceptions Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-07-15 15:54 ` Pavel Stehule <pavel.stehule@gmail.com>
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-15 14:10 User defined exceptions Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-07-17 07:34 ` Alexey Bashtanov <alexey_bashtanov@ocslab.com>
2015-07-17 15:31 ` Re: User defined exceptions 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-15 14:10 User defined exceptions Alex Ignatov <a.ignatov@postgrespro.ru>
2015-07-17 07:34 ` Re: User defined exceptions Alexey Bashtanov <alexey_bashtanov@ocslab.com>
@ 2015-07-17 15:31 ` Alex Ignatov <a.ignatov@postgrespro.ru>
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
* Re: User defined exceptions
2015-07-15 14:10 User defined exceptions Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-07-17 07:36 ` Alexey Bashtanov <bashtanov@imap.cc>
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
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