From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: Peter.Gabriel@gmx.de
Subject: Re: handling error in a function
Date: Wed, 18 Dec 2002 10:04:19 +0100
Message-ID: <3E003A13.DFAD08B9@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, Peter.Gabriel@gmx.de
Subject: Re: handling error in a function
In-Reply-To: <3E003A13.DFAD08B9@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox