DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=RvoC88slfWE+WyvkG3ivTEDdjdu1COiLYjkIYnIstC2nBb9NESZZyobt+7codH1lqud0uonmGbaIa+FfPbsSmFGtGss64aidYDh01c+LRLlGDCyZw7W5ZaImOFYYehickEhsBf4lWbnE8KEa3XNXtSIDaSNhoOOeQucbDLdzi3A=
Message-ID: <fc63e1af05041811327a8b3061@mail.gmail.com>
Date: Mon, 18 Apr 2005 22:32:26 +0400
From: "A. Kulikov" <a.kulikov@gmail.com>
Reply-To: "A. Kulikov" <a.kulikov@gmail.com>
To: pgsql-sql@postgresql.org
Subject: User Defined Functions Errors
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

How to I return an error from inside a user defined function? For
example the following:

CREATE or REPLACE FUNCTION drop_node (integer) RETURNS text
AS '

DECLARE

mleft INTEGER;=20
mright INTEGER;

BEGIN

-- Check if the desired node exists
SELECT lft, rgt FROM structure WHERE id =3D $1 INTO mleft, mright;

IF mleft IS NULL THEN
  RETURN ''No entry found with an id of ''||$2;
END IF;

-- Drop the node and its subtree
DELETE FROM structure WHERE lft >=3D mleft AND rgt <=3D mright;

-- Close the gap
UPDATE structure SET rgt =3D rgt - (mright - mleft + 1) WHERE rgt > mright;
UPDATE structure SET lft =3D lft - (mright - mleft + 1) WHERE lft > mleft;

RETURN ''ok'';

END;
'
LANGUAGE 'plpgsql';

Should be terminated with an error @ RETURN "No Entry found"; instead
of returning the error text.

best regards and thanks,

Alex
--=20
The mind is essential -- http://essentialmind.com/