Thread: handling error in a function

Hi all!

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 ERRORRETURN 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? 

(In MS-SQL there is @@ERROR to indicate a database error and i can handle
it, in Oracle nearly the same ...)

Please help :-)

Regards,
peter


-- 
+++ GMX - Mail, Messaging & more  http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!

>
> 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

Peter Gabriel wrote:

>
> 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.

I don't know, how PHP works with Postgresql, but in psql I get such 
error, when deleting wrong row:
ERROR:  <unnamed> referential integrity violation - key in table_master 
still referenced from table_detail

Maybe it is possible some way to access this error inside PHP?

Tomasz Myrta