Обсуждение: Any way to handle exceptions in PL/pgSQL ?

Is there any way to handle failed queries in PL/pgSQL?

<< I am aware that the example can be re-worded in a single query; This
is a question more on how to do the 'procedural' part >>

Example:1st queryif OK, return result2ne queryif OK, return resultreturn error message

Is it correct, that if a function fails, it returns null ??


-- Function: message_get(varchar)
CREATE FUNCTION message_get(varchar) RETURNS varchar AS '
DECLAREp_key ALIAS FOR $1;v_RetValue Message.MsgText%TYPE;
BEGINSELECT MsgText INTO v_RetValue FROM User_Message WHERE Value = p_Value;-- if ok ??RETURN v_RetValue;
SELECT Text INTO v_RetValue FROM Sys_MessageWHERE Value = p_Value;-- if ok ??RETURN v_RetValue;
RETURN 'nothing found';
END;
' LANGUAGE 'plpgsql';

Cheers,

Jorg Janke      (203) 445-9503                   http://www.compiere.org
Smart ERP & CRM Business Solution for Distribution and Service  globally
------------------------------------------------------------------------
General questions/issues:   http://sourceforge.net/forum/?group_id=29057
Support via:  http://sourceforge.net/tracker/?group_id=29057&atid=410216
------------------------------------------------------------------------

TRY...

CREATE FUNCTION message_get(varchar) RETURNS varchar AS '
DECLARE
p_key ALIAS FOR $1;
v_RetValue Message.MsgText%TYPE;
BEGIN
SELECT MsgText INTO v_RetValue 
FROM User_Message 
WHERE Value = p_Value;

if FOUND then  RETURN v_RetValue; else
   SELECT Text INTO v_RetValue        FROM Sys_Message       WHERE Value = p_Value;       if FOUND then
RETURNv_RetValue;          else           RETURN 'nothing found';
 
END IF;
END IF;

END;
' LANGUAGE 'plpgsql';


http://www.pgexplorer.com
GUI PostgreSQL Tool


----- Original Message ----- 
From: "Jorg Janke" <jorg.janke@compiere.org>
To: <pgsql-sql@postgresql.org>
Sent: Saturday, June 01, 2002 6:11 AM
Subject: [SQL] Any way to handle exceptions in PL/pgSQL ?


> Is there any way to handle failed queries in PL/pgSQL?
> 
> << I am aware that the example can be re-worded in a single query; This
> is a question more on how to do the 'procedural' part >>
> 
> Example:
> 1st query
> if OK, return result
> 2ne query
> if OK, return result
> return error message
> 
> Is it correct, that if a function fails, it returns null ??
> 
> 
> -- Function: message_get(varchar)
> CREATE FUNCTION message_get(varchar) RETURNS varchar AS '
> DECLARE
> p_key ALIAS FOR $1;
> v_RetValue Message.MsgText%TYPE;
> BEGIN
> SELECT MsgText INTO v_RetValue 
> FROM User_Message 
> WHERE Value = p_Value;
> -- if ok ??
> RETURN v_RetValue;
> 
> SELECT Text INTO v_RetValue 
> FROM Sys_Message
> WHERE Value = p_Value;
> -- if ok ??
> RETURN v_RetValue;
> 
> RETURN 'nothing found';
> END;
> ' LANGUAGE 'plpgsql';
> 
> Cheers,
> 
> Jorg Janke      (203) 445-9503                   http://www.compiere.org
> Smart ERP & CRM Business Solution for Distribution and Service  globally
> ------------------------------------------------------------------------
> General questions/issues:   http://sourceforge.net/forum/?group_id=29057
> Support via:  http://sourceforge.net/tracker/?group_id=29057&atid=410216
> ------------------------------------------------------------------------
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html