VARIABLE DECLARATION
Description
Declare all variables in a block, except for loop variables, in the block's DECLARE section.
Variable declarations are fully supported by Snowflake.
Grammar Syntax
DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];
In Redshift, the CONSTANT
keyword prevents variable reassignment during execution. Since Snowflake does not support this keyword, it is removed during transformation. This does not impact functionality, as the logic should not attempt to reassign a constant variable.
The NOT NULL
constraint in Redshift ensures a variable cannot be assigned a null value and requires a non-null default value. As Snowflake does not support this constraint, it is removed during transformation. However, the default value is retained to maintain functionality.
A variable declare with a Refcursor is transformed to Resultset type, for more information.
Sample Source Patterns
Input Code:
CREATE OR REPLACE PROCEDURE VARIABLE_DECLARATION()
LANGUAGE plpgsql
AS $$
DECLARE
v_simple_int INT;
v_default_char CHAR(4) DEFAULT 'ABCD';
v_default_float FLOAT := 10.00;
v_constant_char CONSTANT CHAR(4) := 'ABCD';
v_notnull VARCHAR NOT NULL DEFAULT 'Test default';
v_refcursor REFCURSOR;
BEGIN
-- Procedure logic
END;
$$;
Output Code:
CREATE OR REPLACE PROCEDURE VARIABLE_DECLARATION ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "03/03/2025", "domain": "test" }}'
AS $$
DECLARE
v_simple_int INT;
v_default_char CHAR(4) DEFAULT 'ABCD';
v_default_float FLOAT := 10.00;
v_constant_char CHAR(4) := 'ABCD';
--** SSC-FDM-PG0012 - NOT NULL CONSTRAINT HAS BEEN REMOVED. ASSIGNING NULL TO THIS VARIABLE WILL NO LONGER CAUSE A FAILURE. **
v_notnull VARCHAR DEFAULT 'Test default';
v_refcursor RESULTSET;
BEGIN
NULL;
-- Procedure logic
END;
$$;
Known Issues
No issues were found.
Related EWIs
SSC-FDM-PG0012: NOT NULL constraint has been removed. Assigning NULL to this variable will no longer cause a failure.
Last updated