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

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>:
> 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

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

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

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