Re: User defined EXCEPTIONs - Mailing list pgsql-general

From vishal saberwal
Subject Re: User defined EXCEPTIONs
Date
Msg-id 3e74dc250603071048q610ef8bcuc0c0ddcbac44fa14@mail.gmail.com
Whole thread Raw
In response to User defined EXCEPTIONs  ("vishal saberwal" <vishalsaberwal@gmail.com>)
List pgsql-general
I have a work around, but it would really help if there was a way to define my own exceptions (for business logic),

Here is the output, for those who would be browsing for it in future,

create or replace function RowCount_Select(varchar) returns int as $$
DECLARE
    res int;
BEGIN
    select into res reltuples from pg_class where relkind='r' and relname=$1;
    if not found then
        raise exception 'testing';
    end if;
    return res;
END;
$$ language plpgsql strict;

create or replace function test1() returns int as $$
DECLARE
    res int;
BEGIN
    select into res RowCount_Select('test');
    return res;
EXCEPTION
    when raise_exception then
        raise exception 'test2 # %',SQLERRM;
END;
$$ language plpgsql strict;

DB=# select * from test1();
ERROR:  test2 # testing

this does solve my problem of catching my exceptions to a good extent.

On 3/7/06, vishal saberwal <vishalsaberwal@gmail.com> wrote:
Using postgreSQL 8.1 on fedora.

Below is a function RowCount_Select(Table_Name) defined that raises exception (test1) which i intend to catch in test1( ) and raise exception (test2).
Do we have a way to catch such user defined exceptions? Can someone direct me to the right resource pages?

create or replace function RowCount_Select(varchar) returns int as $$
DECLARE
    res int;
BEGIN
    select into res reltuples from pg_class where relkind='r' and relname=$1;
    if not found then
        raise exception 'test1';
    end if;
    return res;
END;
$$ language plpgsql strict;

-- There is no relation by name ' test '
create or replace function test1() returns int as $$
DECLARE
    res int;
BEGIN
    select into res RowCount_Select('test');
    return res;
EXCEPTION
    when test1 then
        raise exception 'test2';
END;
$$ language plpgsql strict;

ERROR:  unrecognized exception condition "test1"
CONTEXT:  compile of PL/pgSQL function "test1" near line 11

thanks,
vish

pgsql-general by date:

Previous
From: "vishal saberwal"
Date:
Subject: User defined EXCEPTIONs
Next
From: Tom Lane
Date:
Subject: Re: how to setup default privileges