SSC-EWI-PG0013

Raise exception translated to user-defined stored procedure.

Severity

Medium

Description

This error is added when the Raise exception is transformed to a user-defined stored procedure.

Also be aware that Raise Exception outside an Exception Block is not supported because it requires creating a custom exception based on dynamic data, which is not supported by Snowflake. Snowflake only supports the creation of new exceptions with a literal message.

Code Example

Input Code:

Greenplum

IN -> Greenplum_01.sql

CREATE FUNCTION FunctionName1() RETURNS void
AS 
$$
DECLARE 
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'exception1';
	v_var2 := 'exception2';
	RAISE EXCEPTION 'Exception %',v_var1;
    EXCEPTION 
      WHEN OTHERS THEN
         RAISE EXCEPTION '%',v_var2;
         RETURN;
END;
$$
    LANGUAGE plpgsql NO SQL;

Output Code:

Snowflake

OUT -> Greenplum_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-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 := 'exception1';
	v_var2 := 'exception2';
	LET SC_RAISE_MESSAGE VARCHAR;
	SC_RAISE_MESSAGE := STRING_FORMAT_UDF('Exception %', array_construct(:v_var1));
	!!!RESOLVE EWI!!! /*** SSC-EWI-PG0013 - RAISE EXCEPTION TRANSLATED TO USER-DEFINED STORED PROCEDURE CALL. ***/!!!
	CALL RAISE_MESSAGE_UDF('EXCEPTION', :SC_RAISE_MESSAGE);
	RETURN 'SUCCESS';
    EXCEPTION
      WHEN OTHER THEN
		SC_RAISE_MESSAGE := STRING_FORMAT_UDF('%', array_construct(:v_var2));
		!!!RESOLVE EWI!!! /*** SSC-EWI-PG0013 - RAISE EXCEPTION TRANSLATED TO USER-DEFINED STORED PROCEDURE CALL. ***/!!!
		CALL RAISE_MESSAGE_UDF('EXCEPTION', :SC_RAISE_MESSAGE);
		RAISE;
         RETURN;
END;
$$;

These are the UDFs used in the transformation:

STRING_FORMAT_UDF

CREATE OR REPLACE FUNCTION STRING_FORMAT_UDF(PATTERN VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "09/09/2024" }}'
AS
$$
	var placeholder_str = "{%}";
	var result = PATTERN.replace(/(?<!%)%(?!%)/g, placeholder_str).replace("%%","%");
	for (var i = 0; i < ARGS.length; i++)
	{
		result = result.replace(placeholder_str, ARGS[i]);
	}
	return result;
$$;

RAISE_MESSAGE_UDF

CREATE OR REPLACE PROCEDURE RAISE_MESSAGE_UDF(LEVEL VARCHAR, MESSAGE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "09/09/2024" }}'
AS
$$
  BEGIN
    RETURN 'Logged message: ' || LEVEL || ' - ' || MESSAGE;
  END;
$$;

Recommendations

• If you need more support, you can email us at [e-mail]