How to manually raise exception

Question

Hi friends,  
  
I see in the docs  "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

ethanstark · Answer

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

KalyanN-aws · Answer

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

How to manually raise exception

Relevant content