Date: Tue, 17 Dec 2002 20:42:08 +0100 (MET)
From: Peter Gabriel <Peter.Gabriel@gmx.de>
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Subject: handling error in a function
Message-ID: <29166.1040154128@www58.gmx.net>
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

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

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