How to manually raise exception

0

Hi friends,

I see in the docs https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-messages-errors "raise" is a supported keyword. However, it is not clear how to use it. The below example throws an error regarding "syntax error near exception".

CREATE TEMP TABLE sample (product varchar(10),  order_num varchar(20));
INSERT INTO sample (product, order_num) values
        ('key1', '1'),
        ('key1', '1'),
        ('key2', '2');

select
    CASE
        WHEN product = 'key1' THEN True
        ELSE
            RAISE EXCEPTION 'Test Error!'
        END
from sample;

How do I throw an exception?

Edited by: JonnyFuller on May 12, 2020 8:31 AM

asked 4 years ago2797 views
2 Answers
0

Hi Jonny,

RAISE is a procedural command. It cannot be used in a SELECT statement. You can use it in a stored procedure, like this :

CREATE OR REPLACE PROCEDURE test_sp(f1 int, f2 int)
AS $$
BEGIN
    IF f1>f2 THEN
        RAISE INFO '% is greater than %', f1, f2;
    ELSE
        RAISE EXCEPTION 'Error : % is smaller than %', f1, f2;
    END IF;
END;
$$ LANGUAGE plpgsql;

You can call a stored procedure with a CALL command, like this :

call test_sp(2,1);

I hope this helps.
Ethan

answered 4 years ago
0

Hi Jonny,

You may use "RAISE" in Procedure , like below:

CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$
BEGIN
FOR b in 1..10 LOOP
IF b < a THEN
RAISE INFO 'b = %', b;
ELSE
RETURN;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

[] Supported PL/pgSQL statements - RAISE - https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-messages-errors

answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions