pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAny way to handle exceptions in PL/pgSQL ?
2+ messages / 2 participants
[nested] [flat]
* Any way to handle exceptions in PL/pgSQL ?
@ 2002-06-01 04:11 Jorg Janke <jorg.janke@compiere.org>
0 siblings, 1 reply; 2+ messages in thread
From: Jorg Janke @ 2002-06-01 04:11 UTC (permalink / raw)
To: pgsql-sql
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
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Any way to handle exceptions in PL/pgSQL ?
@ 2002-06-01 10:01 PG Explorer <pgmail@pgexplorer.com>
parent: Jorg Janke <jorg.janke@compiere.org>
0 siblings, 0 replies; 2+ messages in thread
From: PG Explorer @ 2002-06-01 10:01 UTC (permalink / raw)
To: pgsql-sql
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" <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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-06-01 10:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-01 04:11 Any way to handle exceptions in PL/pgSQL ? Jorg Janke <jorg.janke@compiere.org>
2002-06-01 10:01 ` PG Explorer <pgmail@pgexplorer.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox