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

Search Discussions

  • Pavel Stehule at Aug 11, 2012 at 8:47 pm
    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
  • Andrus at Aug 12, 2012 at 7:31 am
    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 );
  • Pavel Stehule at Aug 12, 2012 at 9:12 am

    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 );
  • Andrus at Aug 12, 2012 at 10:02 am

    ... 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' );
  • Craig Ringer at Aug 12, 2012 at 12:53 pm

    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;
  • Craig Ringer at Aug 12, 2012 at 3:27 am

    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
  • Craig Ringer at Aug 12, 2012 at 5:07 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 11, '12 at 7:07p
activeAug 12, '12 at 12:53p
posts8
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase