How to raise error from PostgreSql SQL statement if some condition is met
I’m looking for a way to raise error from sql select if some condition is met.
Tried code below to got error shown in comment.
How to fix ?
Andrus
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
Hello
You can execute only SQL statements - RAISE is plpgsql statement, not
SQL statement, so you cannot execute it.
why you don't use just
CREATE OR REPLACE FUNCTION raise_exception(text)
RETURNS void AS $$
BEGIN
RAISE EXCEPTION '%', $1;
END;
$$ LANGUAGE plpgsql;
SELECT raise_exception('bubu');
Regards
Pavel Stehule
2012/8/11 Andrus <kobruleht2@hot.ee>:
Show quoted text
I’m looking for a way to raise error from sql select if some condition is
met.
Tried code below to got error shown in comment.
How to fix ?Andrus
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
On 08/12/2012 03:07 AM, Andrus wrote:
Iļæ½m looking for a way to raise error from sql select if some condition
is met.
Tried code below to got error shown in comment.
How to fix ?
Create a small pl/pgsql function that RAISEs a message, and call that
from your EXECUTEd SQL via a CASE ... WHEN .
--
Craig Ringer
On 08/12/2012 03:07 AM, Andrus wrote:
Iļæ½m looking for a way to raise error from sql select if some condition
is met.
Tried code below to got error shown in comment.
For anyone reading this later, Andrus also posted this on Stack Overflow:
http://stackoverflow.com/questions/11916838/how-to-execute-postgresql-raise-command-dynamically
Andrus: If you post in multiple places please say so and link between
them. It stops people wasting their time and helps others find the
answers when searching later.
--
Craig Ringer
Than you very much.
It worked.
I tried to extend it to pass message parameters. Tried code below but got
syntax error. How to pass message parameters ?
Andrus.
CREATE OR REPLACE FUNCTION RaiseException(text, variadic )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION $1, $2;
END;
$BODY$;
SELECT RaiseException('Exception Param1=% Param2=%', 'textvalue', 2 );
2012/8/12 Andrus <kobruleht2@hot.ee>:
Than you very much.
It worked.I tried to extend it to pass message parameters. Tried code below but got
syntax error. How to pass message parameters ?Andrus.
CREATE OR REPLACE FUNCTION RaiseException(text, variadic )
... RaiseException(text, variadic text[])
..
VARIADIC is keyword, not datatype
Regards
Pavel Stehule
Show quoted text
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION $1, $2;
END;
$BODY$;SELECT RaiseException('Exception Param1=% Param2=%', 'textvalue', 2 );
... RaiseException(text, variadic text[])
..
VARIADIC is keyword, not datatype
Thank you.
I tried code below but got error shown in comment.
No idea what I'm doing wrong.
Andrus.
CREATE OR REPLACE FUNCTION RaiseException(text, variadic text[] )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
-- ERROR: syntax error at or near "$1"
RAISE EXCEPTION $1, $2;
END;
$BODY$;
SELECT RaiseException('Exception Param1=% Param2=%', 'textvalue', '2' );
On 08/12/2012 06:02 PM, Andrus wrote:
... RaiseException(text, variadic text[])
..VARIADIC is keyword, not datatype
Thank you.
I tried code below but got error shown in comment.
No idea what I'm doing wrong.Andrus.
CREATE OR REPLACE FUNCTION RaiseException(text, variadic text[] )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
-- ERROR: syntax error at or near "$1"
RAISE EXCEPTION $1, $2;
You probably want something like:
RAISE EXCEPTION "%: %", $1, $2;