RAISE STATEMENT

Description

Use the RAISE statement to report messages and raise errors.

Click here to navigate to the PostgreSQL documentation page for this syntax.

Notice that this translation spec applies only to RAISE statements that have level with the value of NOTICE and EXCEPTION.

Raise Statement is translated to a Procedure Call

Grammar Syntax

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

Sample Source Patterns with RAISE NOTICE

Greenplum

CREATE FUNCTION FunctionName1() RETURNS void
    AS $$
declare 
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'First';
	v_var2 := 'Second';
	RAISE NOTICE '% - Current schema is %',v_var1, v_var2;
END;
$$
    LANGUAGE plpgsql NO SQL;

Snowflake

/*** MSC-WARNING - MSC-GP0002 - NON-RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/
CREATE OR REPLACE PROCEDURE FunctionName1 () RETURNS STRING
    LANGUAGE SQL
    AS $$
declare
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'First' /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'AssignStat' NODE ***/;
	v_var2 := 'Second' /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'AssignStat' NODE ***/;
	LET SC_RAISE_MESSAGE VARCHAR;
	SC_RAISE_MESSAGE := STRING_FORMAT_UDF('% - Current schema is %', array_construct(:v_var1, :v_var2));
	--** MSC-INFORMATION - MSCINF0040 - RAISE NOTICE TRANSLATED TO USER-DEFINED STORED PROCEDURE **
	CALL RAISE_MESSAGE_UDF('NOTICE', :SC_RAISE_MESSAGE);
	RETURN 'SUCCESS';
END;
$$;

Sample Source Patterns with RAISE EXCEPTION

Greenplum

CREATE FUNCTION FunctionName1() RETURNS void
    AS $$
declare 
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'Hola';
	v_var2 := 'Adios';
	RAISE EXCEPTION '% - Current schema is %',v_var1, v_var2;
    EXCEPTION 
      WHEN OTHERS THEN
         RAISE EXCEPTION '% - %',v_var1, v_var2;
         RETURN;
END;
$$
    LANGUAGE plpgsql NO SQL;

Snowflake

/*** MSC-WARNING - MSC-GP0002 - NON-RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/
CREATE OR REPLACE PROCEDURE FunctionName1 () RETURNS STRING
    LANGUAGE SQL
    AS $$
declare
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'Hola' /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'AssignStat' NODE ***/;
	v_var2 := 'Adios' /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'AssignStat' NODE ***/;
	LET SC_RAISE_MESSAGE VARCHAR;
	SC_RAISE_MESSAGE := STRING_FORMAT_UDF('% - Current schema is %', array_construct(:v_var1, :v_var2));
	--** MSC-ERROR - MSC-PG0013 - UNSUPPORTED RAISE EXCEPTION, CREATING A DYNAMIC CUSTOM EXCEPTION IS NOT SUPPORTED IN SNOWFLAKE. **
	CALL RAISE_MESSAGE_UDF('EXCEPTION', :SC_RAISE_MESSAGE);
	RETURN 'SUCCESS';
    EXCEPTION
      WHEN OTHER THEN
        SC_RAISE_MESSAGE := STRING_FORMAT_UDF('% - %', array_construct(:v_var1, :v_var2));
        --** MSC-INFORMATION - MSCINF0042 - RAISE EXCEPTION TRANSLATED TO USER-DEFINED STORED PROCEDURE CALL AND RAISE STAT **
        CALL RAISE_MESSAGE_UDF('EXCEPTION', :SC_RAISE_MESSAGE);
        RAISE;
         RETURN;
END;
$$;

Related EWIs

1. MSCINF0040: Raise notice translated to user-defined stored procedure.

2. MSC-PG0013: Unsupported Raise Exception.

3. MSCINF0042: Raise Exception translated to user-defined stored procedure call and raise stat.