FETCH CURSOR
Description
Retrieves rows using a cursor. (Redshift SQL Language reference Fetch)
Transformation information
FETCH [ NEXT | ALL | {FORWARD [ count | ALL ] } ] FROM cursor
FETCH cursor INTO target [, target ...];
Sample Source Patterns
Setup data
Redshift
CREATE TABLE cursor_example
(
col1 INTEGER,
col2 VARCHAR(20)
);
INSERT INTO cursor_example VALUES (10, 'hello');
Snowflake
CREATE TABLE cursor_example
(
col1 INTEGER,
col2 VARCHAR(20)
);
INSERT INTO cursor_example VALUES (10, 'hello');
Fetch into
The FETCH into statement from Redshift is fully equivalent in Snowflake
Redshift
CREATE OR REPLACE PROCEDURE fetch_into_example()
LANGUAGE plpgsql
AS $$
DECLARE my_cursor CURSOR FOR
SELECT col1, col2
FROM cursor_example;
some_id INT;
message VARCHAR(20);
BEGIN
OPEN my_cursor;
FETCH my_cursor INTO some_id, message;
CLOSE my_cursor;
INSERT INTO cursor_example VALUES (some_id * 10, message || ' world!');
END;
$$;
CALL fetch_into_example();
SELECT * FROM cursor_example;
Snowflake
CREATE OR REPLACE PROCEDURE fetch_into_example ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
my_cursor CURSOR FOR
SELECT col1, col2
FROM
cursor_example;
some_id INT;
message VARCHAR(20);
BEGIN
OPEN my_cursor;
FETCH my_cursor INTO some_id, message;
CLOSE my_cursor;
INSERT INTO cursor_example
VALUES (:some_id * 10, :message || ' world!');
END;
$$;
CALL fetch_into_example();
SELECT * FROM
cursor_example;
Known Issues
1. Fetch without target variables is not supported
Snowflake requires the FETCH statement to specify the INTO clause with the variables where the fetched row values are going to be stored. When a FETCH statement is found in the code with no INTO clause an EWI will be generated.
Input Code:
FETCH FORWARD FROM cursor1;
Output Code:
!!!RESOLVE EWI!!! /*** SSC-EWI-PG0015 - FETCH CURSOR WITHOUT TARGET VARIABLES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FETCH FORWARD FROM cursor1;
Known Issues
There are no known issues.
Related EWIs
SSC-EWI-PG0015: Fetch cursor without target variables is not supported in Snowflake
Last updated