How to raise error from PostgreSql SQL statement if some condition is met

[Andrus]

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

[Pavel Stehule]

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

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

-
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Andrus]

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 );


-
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Pavel Stehule]

Post by Andrus
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

Post by Andrus
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION $1, $2;
END;
$BODY$;
SELECT RaiseException('Exception Param1=% Param2=%', 'textvalue', 2 );

-
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Andrus]

Post by Pavel Stehule
... 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' );

-
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

Post by Andrus

Post by Pavel Stehule
... 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;

-
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

Post by Andrus
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

[Craig Ringer]

Post by Andrus
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