Refcursor declarations are fully supported by Snowflake.
Grammar Syntax
DECLARE
name refcursor;
Since Snowflake does not support the REFCURSOR data type, its functionality is replicated by converting the REFCURSOR variable into a RESULTSET type. The query used to open the REFCURSOR is assigned to the RESULTSET variable, after which a new cursor is created and linked to the RESULTSET variable. Additionally, all references to the original REFCURSOR within the cursor logic are updated to use the new cursor, thereby replicating the original functionality.
Sample Source Patterns
Case: Single use
Input Code:
IN -> Redshift_01.sql
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR()
LANGUAGE plpgsql
AS $$
DECLARE
v_curs1 refcursor;
BEGIN
OPEN v_curs1 FOR SELECT column1_name, column2_name FROM your_table;
-- Cursor logic
CLOSE v_curs1;
END;
$$;
Output Code:
OUT -> Redshift_01.sql
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR ()
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_curs1 RESULTSET;
BEGIN
v_curs1 := (
SELECT column1_name, column2_name FROM your_table
);
LET v_curs1_Resultset_1 CURSOR
FOR
v_curs1;
OPEN v_curs1_Resultset_1;
-- Cursor logic
CLOSE v_curs1_Resultset_1;
END;
$$;
Case: Cursor with Dynamic Sql
Input Code:
IN -> Redshift_02.sql
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC(min_salary NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
cur refcursor;
qry TEXT;
BEGIN
qry := 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
OPEN cur FOR EXECUTE qry;
-- Cursor logic
CLOSE cur;
END;
$$;
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC2(min_salary NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
cur refcursor;
BEGIN
OPEN cur FOR EXECUTE 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
-- Cursor logic
CLOSE cur;
END;
$$;
Output Code:
OUT -> Redshift_02.sql
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC (min_salary NUMERIC)
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
cur RESULTSET;
qry TEXT;
BEGIN
qry := 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
cur := (
EXECUTE IMMEDIATE qry
);
LET cur_Resultset_1 CURSOR
FOR
cur;
OPEN cur_Resultset_1;
-- Cursor logic
CLOSE cur_Resultset_1;
END;
$$;
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC2 (min_salary NUMERIC)
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
cur RESULTSET;
BEGIN
cur := (
EXECUTE IMMEDIATE 'SELECT id, name FROM employees WHERE salary > ' || min_salary
);
LET cur_Resultset_2 CURSOR
FOR
cur;
OPEN cur_Resultset_2;
-- Cursor logic
CLOSE cur_Resultset_2;
END;
$$;
Case: Multiple uses:
Input Code:
IN -> Redshift_03.sql
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR()
LANGUAGE plpgsql
AS $$
DECLARE
v_curs1 refcursor;
BEGIN
OPEN v_curs1 FOR SELECT column1_name, column2_name FROM your_table;
-- Cursor logic
CLOSE v_curs1;
OPEN v_curs1 FOR SELECT column3_name, column4_name FROM your_table2;
-- Cursor logic
CLOSE v_curs1;
END;
$$;
Output Code:
OUT -> Redshift_03.sql
CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR ()
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_curs1 RESULTSET;
BEGIN
v_curs1 := (
SELECT column1_name, column2_name FROM your_table
);
LET v_curs1_Resultset_1 CURSOR
FOR
v_curs1;
OPEN v_curs1_Resultset_1;
-- Cursor logic
CLOSE v_curs1_Resultset_1;
v_curs1 := (
SELECT column3_name, column4_name FROM your_table2
);
LET v_curs1_Resultset_2 CURSOR
FOR
v_curs1;
OPEN v_curs1_Resultset_2;
-- Cursor logic
CLOSE v_curs1_Resultset_2;
END;
$$;
