How to raise error from PostgreSQL SQL statement if some condition is met?
I tried code below but got error.
CREATE OR REPLACE FUNCTION "exec"(text)
RETURNS text AS
$BODY$
BEGIN
EXECUTE $1;
RETURN $1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
-- ERROR: syntax error at or near "raise"
-- LINE 1: raise 'test'
SELECT exec('raise ''test'' ') WHERE TRUE
In real application TRUE
is replaced by some condition.
Update
I tried to extend answer to pass exception message parameters.
Tried code below but got syntax error.
How to pass message parameters ?
CREATE OR REPLACE FUNCTION exec(text, variadic )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION $1, $2;
END;
$BODY$;
SELECT exec('Exception Param1=% Param2=%', 'param1', 2 );
You cannot call RAISE
dynamically (with EXECUTE
) in PL/pgSQL - that only works for SQL statements, and RAISE
is a PL/pgSQL command.
Use this simple function instead:
CREATE OR REPLACE FUNCTION f_exec(text)
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION '%', $1;
END;
$BODY$;
Call:
SELECT f_exec('My message is empty!');
I wrote more in this related answer on dba.SE: explanation, links, more options for the function, context (including line number) from the function call etc.
Additional answer to comment
CREATE OR REPLACE FUNCTION f_exec1(VARIADIC text[])
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION 'Reading % % %!', $1[1], $1[2], $1[3];
END;
$BODY$;
Call:
SELECT f_exec1('the','manual','educates');
VARIADIC
is not a data type, but an argument mode.
Elements have to be handled like any other array element.
To use multiple variables in a RAISE
statement, put multiple %
into the message text.
The above example will fail if no $3 is passed to the call. You'd have to assemble a string from the variable number of input elements. Example:
CREATE OR REPLACE FUNCTION f_exec2(VARIADIC _arr text[])
RETURNS void LANGUAGE plpgsql AS
$BODY$
DECLARE
_msg text := array_to_string(_arr, ' and '); -- simple string construction
BEGIN
RAISE EXCEPTION 'Reading %!', _msg;
END;
$BODY$;
Call:
SELECT f_exec2('the','manual','educates');
I doubt you need a VARIADIC
parameter for this at all. Read the manual here.
Instead, define all parameters, maybe add defaults:
CREATE OR REPLACE FUNCTION f_exec3(_param1 text = ''
,_param2 text = ''
,_param3 text = 'educates')
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION 'Reading % % %!', $1, $2, $3;
END;
$BODY$;
Call:
SELECT f_exec3('the','manual','educates');
SELECT f_exec3(); -- defaults kick in