Have a try with RAISE NOTE or RAISE EXCEPTION keep in mind that exceptions should be exceptional. So a good idea of whether to use them is to ask 'Do I expect such error' or 'is an explicit error useful for the caller'. I'ts often better to just return an empty relation
|-----Original Message----- |From: A. Kulikov [mailto:[EMAIL PROTECTED] |Sent: Montag, 18. April 2005 20:32 |To: pgsql-sql@postgresql.org |Subject: [SQL] User Defined Functions Errors | | |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; |mright INTEGER; | |BEGIN | |-- Check if the desired node exists |SELECT lft, rgt FROM structure WHERE id = $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 >= mleft AND rgt <= mright; | |-- Close the gap |UPDATE structure SET rgt = rgt - (mright - mleft + 1) WHERE |rgt > mright; |UPDATE structure SET lft = 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 |-- |The mind is essential -- http://essentialmind.com/ | |---------------------------(end of |broadcast)--------------------------- |TIP 4: Don't 'kill -9' the postmaster | ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
