Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 007884764DD for ; Sat, 1 Jun 2002 06:02:07 -0400 (EDT) Received: from homer.websystems.co.za (unknown [196.25.70.70]) by postgresql.org (Postfix) with ESMTP id C9E1147645D for ; Sat, 1 Jun 2002 06:02:03 -0400 (EDT) Received: from hano (bnb53-01-p16.nt.saix.net [155.239.180.16]) by homer.websystems.co.za (8.11.0/8.11.0) with SMTP id g51AM0802598 for ; Sat, 1 Jun 2002 12:22:00 +0200 Message-ID: <003d01c20953$5f9793a0$c80ba8c0@sabex.com> From: "PG Explorer" To: References: <6E2C6F49B3E09548A38EDCAD31C4182F04DDA0@MAIN.compiere.org> Subject: Re: Any way to handle exceptions in PL/pgSQL ? Date: Sat, 1 Jun 2002 12:01:58 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200206/2 X-Sequence-Number: 7967 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 RETURN v_RetValue; else RETURN 'nothing found'; END IF; END IF; END; ' LANGUAGE 'plpgsql'; http://www.pgexplorer.com GUI PostgreSQL Tool ----- Original Message ----- From: "Jorg Janke" To: 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