RAISE

Description

Use the RAISE level statement to report messages and raise errors.

(Redshift SQL Language Reference RAISE)

Variable declarations are fully supported by Snowflake.

Grammar Syntax

RAISE level 'format' [, variable [, ...]];

In Amazon Redshift, the RAISE statement is used to generate messages in the console or throw custom exceptions. Redshift allows you to specify different levels to indicate the severity of the message. In Snowflake, this functionality can be emulated using a user-defined function (UDF) that makes a call to the console depending on the specified level.

1. Exception: When the level is "EXCEPTION", a custom exception is raised with a general message: "To view the EXCEPTION MESSAGE, you need to check the log." The exception code is -20002, which informs the user that the custom message can be found in the logs. This is due to limitations when sending custom exceptions in Snowflake.

2. Warning: If the level is "WARNING", SYSTEM$LOG_WARN is used to print the warning message to Snowflake's log, which helps highlight potential issues without interrupting the flow of execution.

3. Info: For any other level (such as "INFO"), SYSTEM$LOG_INFO is used to print the message to the console log, providing more detailed feedback about the system's state without causing critical disruptions.

This approach allows emulating Redshift's severity levels functionality, adapting them to Snowflake’s syntax and features, while maintaining flexibility and control over the messages and exceptions generated during execution.

Limitations

• To view logs in Snowflake, it is necessary to have specific privileges, such as the ACCOUNTADMIN or SECURITYADMIN roles.

• Logs in Snowflake are not available immediately and may have a slight delay before the information is visible.

• Personalized error messages in exceptions are not displayed like in Redshift. To view custom messages, you must access the logs directly.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE raise_example(IN user_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
	RAISE EXCEPTION 'User % not exists.', user_id;
END;
$$;

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE raise_example (user_id INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS $$
BEGIN
	CALL RAISE_MESSAGE_UDF('EXCEPTION', 'User % not exists.', array_construct(:user_id));
END;
$$;

UDFs

RAISE_MESSAGE_UDF

CREATE OR REPLACE PROCEDURE RAISE_MESSAGE_UDF(LEVEL VARCHAR, MESSAGE VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
        MY_EXCEPTION EXCEPTION (-20002, 'To view the EXCEPTION MESSAGE, you need to check the log.');
        SC_RAISE_MESSAGE VARCHAR;
    BEGIN
        SC_RAISE_MESSAGE := STRING_FORMAT_UDF(MESSAGE, ARGS);
        IF (LEVEL = 'EXCEPTION') THEN
            SYSTEM$LOG_ERROR(SC_RAISE_MESSAGE);
            RAISE MY_EXCEPTION;
        ELSEIF (LEVEL = 'WARNING') THEN
            SYSTEM$LOG_WARN(SC_RAISE_MESSAGE);
            RETURN 'Warning printed successfully';
        ELSE
            SYSTEM$LOG_INFO(SC_RAISE_MESSAGE);
            RETURN 'Message printed successfully';
        END IF;
    END;
$$;

STRING_FORMAT_UDF

CREATE OR REPLACE FUNCTION PUBLIC.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": "02/11/2025",  "domain": "test" }}'
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;
$$;

Known Issues

There are no known issues.

Related EWIs.

There are no related EWIs.