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

Query

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

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

Snowflake

Query

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

Query

IN -> Redshift_01.sql

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;

Result

+------+-------------+
| col1 | col2        |
+------+-------------+
| 10   | hello       |
| 100  | hello world!|
+------+-------------+

Snowflake

Query

OUT -> Redshift_01.sql

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;

Result

+------+-------------+
| col1 | col2        |
+------+-------------+
| 10   | hello       |
| 100  | hello world!|
+------+-------------+

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

1. SSC-EWI-PG0015: Fetch cursor without target variables is not supported in Snowflake