CREATE OR REPLACE PROCEDURE cursor_test()
AS $$
BEGIN
OPEN cursor1;
END;
$$;
Output Code:
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
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;
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;
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
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);
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);