Working with RESULTSETs¶
This topic explains how to use RESULTSETs in Snowflake Scripting.
Introduction¶
In Snowflake Scripting, a RESULTSET is a SQL data type that points to the result set of a query.
Because a RESULTSET is just a pointer to the results, you must do one of the following to access the results through the RESULTSET:
Use the
TABLE()
syntax to retrieve the results as a table.Iterate over the RESULTSET with a cursor.
Examples of both of these are included below.
Understanding the Differences Between a Cursor and a RESULTSET¶
A RESULTSET and a cursor both provide access to the result set of a query. However, these objects differ in the following ways:
The point in time when the query is executed.
For a cursor, the query is executed when you execute the OPEN command on the cursor.
For a RESULTSET, the query is executed when you assign the query to the RESULTSET (either in the DECLARE section or in the BEGIN … END block).
Support for binding in the OPEN command.
When you declare a cursor, you can specify bind parameters (
?
characters). Later, when you execute the OPEN command, you can bind variables to those parameters in the USING clause.RESULTSET does not support the
OPEN
command.
Note that if you have a cursor and you need to return a table from the Snowflake Scripting block, you can pass the cursor to
RESULTSET_FROM_CURSOR(cursor)
to return a RESULTSET and pass that RESULTSET to TABLE(...)
. See
Returning a Table for a Cursor.
Declaring a RESULTSET¶
You can declare a RESULTSET in the DECLARE section of a block or in the BEGIN … END section of the block.
Within the DECLARE section, use the syntax described in RESULTSET Declaration Syntax. For example:
DECLARE ... res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
Within the BEGIN … END block, use the syntax described in RESULTSET Assignment Syntax. For example:
BEGIN ... LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
Assigning a Query to a Declared RESULTSET¶
To assign the result of a query to a RESULTSET that has already been declared, use the following syntax:
<resultset_name> := ( <query> ) ;
Where:
resultset_name
The name to give the RESULTSET.
The name should be unique within the current scope.
The name must follow the naming rules for Object Identifiers.
query
The query to assign to the RESULTSET.
For example:
DECLARE res RESULTSET; BEGIN res := (SELECT col1 FROM mytable ORDER BY col1); ...
If you need to build a string of SQL dynamically for the query, set query
to
(EXECUTE IMMEDIATE string_of_sql)
. For example:
DECLARE res RESULTSET; col_name VARCHAR; select_statement VARCHAR; BEGIN col_name := 'col1'; select_statement := 'SELECT ' || col_name || ' FROM mytable'; res := (EXECUTE IMMEDIATE :select_statement); RETURN TABLE(res); END;
Note although you can set query
to an EXECUTE IMMEDIATE
statement for a RESULTSET, you cannot do this for a
cursor.
Using a RESULTSET¶
The query for a RESULTSET is executed when the object is associated with that query. For example:
When you declare a RESULTSET and set the
DEFAULT
clause to a query, the query is executed at that point in time.When you use the
:=
operator to assign a query to a RESULTSET, the query is executed at that point in time.
Note
Because a RESULTSET points to the result set of a query (and does not contain the result set of a query), a RESULTSET is valid only as long as the query results are cached (typically 24 hours). For details about query result caching, see Using Persisted Query Results.
Once the query is executed, you can access the results by using a cursor. You can also return the results as a table from a stored procedure.
Using a Cursor to Access Data from a RESULTSET¶
To use a cursor to access the data from a RESULTSET, declare the cursor on the object. For example:
DECLARE
...
res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
c1 CURSOR FOR res;
Note that when you declare a cursor on a RESULTSET, the cursor gets access to the data already in the RESULTSET. Executing the OPEN command on the cursor does not execute the query for the RESULTSET again.
You can then open the cursor and use the cursor to fetch the data.
Note
If the results include GEOGRAPHY values, you must cast the values to the GEOGRAPHY type before passing the values to any functions that expect GEOGRAPHY input values. See Using a Cursor to Retrieve a GEOGRAPHY Value.
Returning a RESULTSET as a Table¶
If you want to return the results that the RESULTSET points to, pass the RESULTSET to TABLE()
. For example:
CREATE PROCEDURE f() RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR) ... RETURN TABLE(my_resultset_1); ...
This is similar to the way that TABLE()
is used with
table functions (such as RESULT_SCAN)
As shown in the example, if you write a stored procedure that returns a table, you must declare the stored procedure as returning a table.
Note
Currently, the TABLE(resultset_name)
syntax is supported only in the
RETURN statement.
Note that even if you have used a cursor to fetch rows from the RESULTSET, the
table returned by TABLE(resultset_name)
still contains all of the rows (not just the rows starting from the cursor’s
internal row pointer).
Limitations of the RESULTSET Data Type¶
Although RESULTSET is a data type, Snowflake does not yet support:
Declaring a column of type RESULTSET.
Declaring a parameter of type RESULTSET.
Declaring a stored procedure’s return type as a RESULTSET.
Snowflake supports RESULTSET only inside Snowflake Scripting.
In addition, you cannot use a RESULTSET directly as a table. For example, the following is invalid:
select * from my_result_set;
Examples of Using a RESULTSET¶
The following sections provide examples of using a RESULTSET:
Setting up the Data For the Examples¶
Many of the examples below use the table and data shown below:
CREATE TABLE t001 (a INTEGER, b VARCHAR); INSERT INTO t001 (a, b) VALUES (1, 'row1'), (2, 'row2');
Example: Returning a Table From a Stored Procedure¶
The following code shows how to declare a RESULTSET and return the results that the RESULTSET points to. The RETURNS
clause in the CREATE PROCEDURE
command declares that the stored procedure returns a table, which contains one column of
type INTEGER.
The RETURN
statement inside the block uses the TABLE()
syntax to return the results as a table.
Create the stored procedure:
CREATE OR REPLACE PROCEDURE test_sp() RETURNS TABLE(a INTEGER) LANGUAGE SQL AS DECLARE res RESULTSET default (select a from t001 order by a); BEGIN RETURN TABLE(res); END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
CREATE OR REPLACE PROCEDURE test_sp() RETURNS TABLE(a INTEGER) LANGUAGE SQL AS $$ DECLARE res RESULTSET default (select a from t001 order by a); BEGIN RETURN TABLE(res); END; $$;Call the stored procedure:
CALL test_sp(); +---+ | A | |---| | 1 | | 2 | +---+Note that you can use the RESULT_SCAN function to process the results of the stored procedure call:
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) ORDER BY 1; +---+ | A | |---| | 1 | | 2 | +---+
Example: Constructing the SQL Statement Dynamically¶
You can construct the SQL dynamically. The following is an example that executes the same query as the stored procedure above but that uses a SQL statement that is constructed dynamically:
CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR) RETURNS TABLE(a INTEGER) LANGUAGE SQL AS DECLARE res RESULTSET; query VARCHAR DEFAULT 'SELECT a FROM ' || :table_name || ' ORDER BY a'; BEGIN res := (EXECUTE IMMEDIATE :query); RETURN TABLE (res); END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR) RETURNS TABLE(a INTEGER) LANGUAGE SQL AS $$ DECLARE res RESULTSET; query VARCHAR DEFAULT 'SELECT a FROM ' || :table_name || ' ORDER BY a'; BEGIN res := (EXECUTE IMMEDIATE :query); RETURN TABLE (res); END; $$ ;
To run the example, call the stored procedure and pass in the table name:
call test_sp_dynamic('t001'); +---+ | A | |---| | 1 | | 2 | +---+
Example: Declaring a RESULTSET Variable Without a DEFAULT Clause¶
The following code shows how to declare a RESULTSET without a DEFAULT clause (i.e. without associating a query with the RESULTSET), and then associate the RESULTSET with a query later.
CREATE OR REPLACE PROCEDURE test_sp_02() RETURNS TABLE(a INTEGER) LANGUAGE SQL AS DECLARE res RESULTSET; BEGIN res := (select a from t001 order by a); RETURN TABLE(res); END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
CREATE OR REPLACE PROCEDURE test_sp_02() RETURNS TABLE(a INTEGER) LANGUAGE SQL AS $$ DECLARE res RESULTSET; BEGIN res := (select a from t001 order by a); RETURN TABLE(res); END; $$;
Example: Using a CURSOR With a RESULTSET¶
The following code shows how to use a cursor to iterate over the rows in a RESULTSET:
Create the stored procedure:
CREATE OR REPLACE PROCEDURE test_sp_03() RETURNS VARCHAR LANGUAGE SQL AS DECLARE accumulator INTEGER DEFAULT 0; res1 RESULTSET DEFAULT (select a from t001 order by a); cur1 CURSOR FOR res1; BEGIN FOR row_variable IN cur1 DO accumulator := accumulator + row_variable.a; END FOR; RETURN accumulator::VARCHAR; END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
CREATE OR REPLACE PROCEDURE test_sp_03() RETURNS INTEGER LANGUAGE SQL AS $$ DECLARE accumulator INTEGER DEFAULT 0; res1 RESULTSET DEFAULT (select a from t001 order by a); cur1 CURSOR FOR res1; BEGIN FOR row_variable IN cur1 DO accumulator := accumulator + row_variable.a; END FOR; RETURN accumulator; END; $$;Call the stored procedure:
CALL test_sp_03(); +------------+ | TEST_SP_03 | |------------| | 3 | +------------+