Message-ID: <3E003A13.DFAD08B9@rodos.fzk.de>
Date: Wed, 18 Dec 2002 10:04:19 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Peter.Gabriel@gmx.de
Subject: Re: handling error in a function
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> i made desperate efforts with handling errors in a function.
>
> I am using functions for encapsulating a few sql-statements. Please
have a
> look at this:
>
> CREATE FUNCTION sp_fdelce(int4) RETURNS int4 AS '
> DECLARE
>      id ALIAS FOR $1;
> BEGIN
>    DELETE FROM f_ces WHERE fce_id = id;
>
>    -- "virtual code follows"
>    IF ERROR
>       RETURN 0;
>    ELSE
>       RETURN 1;
> END;
> '
> LANGUAGE 'plpgsql';
>
> Not difficult. I know. But fce_id is used as a foreign key by other
tables.
> When executing this and violating that constraint (i mustn't delete
that
> row), the function aborts with "unknown error" and i have no way to
return 0 or
> something like that.
>
> I am programming with PHP and PEAR, each time, the result set is an
object
> of type error, the script jumps to an error page, to calm the angry
customers.
>
>
> Especially in this case I don't want to jump to the error page, i want
to
> tell the user with a normal Messageline: Sorry, you mustn't delete
that
> element.
>
> I can't handle this error? Is that right? I really have no way to
catch that
> foreign key violence?
>
Sure you can, but not directly.
Before deleting you should check for the error condition,
possibly by querying system tables especially pg_relcheck.
So, if the error condition matches, don't delete but generate
your message line.
Regards, Christoph