OPEN CURSOR

Description

Before you can use a cursor to retrieve rows, it must be opened. (Redshift SQL Language Reference Open Cursor).

This syntax is fully supported in Snowflake.

Grammar Syntax

OPEN bound_cursor_name [ ( argument_values ) ];

Sample Source Patterns

Setup data

Redshift

Query

CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

CREATE TABLE cursor_example_results
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

Snowflake

Query

CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

CREATE TABLE cursor_example_results
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

Open cursor without arguments

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE cursor_test()
AS $$
BEGIN
   OPEN cursor1;
END;
$$;

Output Code:

Redshift

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE cursor_test ()
RETURNS VARCHAR
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/05/2025",  "domain": "test" }}'
AS $$
BEGIN
   OPEN cursor1;
END;
$$;

Open cursor with arguments

Cursor arguments have to be binded per each one of its uses, SnowConvert will generate the bindings, was well as reorder and repeat the passed values to the OPEN statement as needed to satisfy the bindings.

Redshift

Query

IN -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE cursor_open_test()
LANGUAGE plpgsql
AS $$
DECLARE
    cursor2 CURSOR (val1 VARCHAR(20), val2 INTEGER) FOR SELECT col1 + val2, col2 FROM cursor_example where val1 = col2 and val2 > col1;
    res1 INTEGER;
    res2 VARCHAR(20);
BEGIN
    OPEN cursor2('hello', 50);
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results VALUES (res1, res2);
END;
$$;

call cursor_open_test();

SELECT * FROM cursor_example_results;

Result

+------+-------+
| col1 | col2  |
+------+-------+
| 60   | hello |
+------+-------+

Snowflake

Query

OUT -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE cursor_open_test()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
	cursor2 CURSOR FOR SELECT col1 + ?, col2 FROM
		cursor_example
	where
		RTRIM(?) = RTRIM( col2) and ? > col1;
	res1 INTEGER;
	res2 VARCHAR(20);
BEGIN
    OPEN cursor2 USING (50, 'hello', 50);
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results
			VALUES (:res1, :res2);
END;
$$;

call cursor_open_test();

SELECT * FROM
	cursor_example_results;

Result

+------+-------+
| col1 | col2  |
+------+-------+
| 60   | hello |
+------+-------+

Open cursor with procedure parameters or local variables

The procedure parameters or local variables have to be binded per each one of its uses in the cursor query, SnowConvert will generate the bindings and add the parameter or variable names to the OPEN statement, even if the cursor originally had no parameters.

Redshift

Query

IN -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE cursor_open_test(someValue iNTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    charVariable VARCHAR(20) DEFAULT 'hello';
    cursor2 CURSOR FOR SELECT col1 + someValue, col2 FROM cursor_example where charVariable = col2 and someValue > col1;
    res1 INTEGER;
    res2 VARCHAR(20);
BEGIN
    OPEN cursor2;
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results VALUES (res1, res2);
END;
$$;

call cursor_open_test(30);

Result

+------+-------+
| col1 | col2  |
+------+-------+
| 40   | hello |
+------+-------+

Snowflake

Query

OUT -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE cursor_open_test (someValue iNTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
	charVariable VARCHAR(20) DEFAULT 'hello';
	cursor2 CURSOR FOR SELECT col1 + ?, col2 FROM
		cursor_example
	where
		RTRIM(?) = RTRIM( col2) and ? > col1;
	res1 INTEGER;
	res2 VARCHAR(20);
BEGIN
    OPEN cursor2 USING (someValue, charVariable, someValue);
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results
			VALUES (:res1, :res2);
END;
$$;

call cursor_open_test(30);

Result

+------+-------+
| col1 | col2  |
+------+-------+
| 40   | hello |
+------+-------+

Known Issues

There are no known issues.

Related EWIs.

There are no related EWIs.