Working with RESULTSETs | Snowflake Documentation (original) (raw)

This topic explains how to use a RESULTSET 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 theOPEN command, you can bind variables to those parameters in the USING clause.
  • RESULTSET does not support the OPEN command. However, you can bind variables in SQL commands before returning the result set.

In general, it is simpler to use a RESULTSET when you want to return a table that contains the result set of a query. However, you can also return a table from a Snowflake Scripting block with a cursor. To do so, you can pass the cursor toRESULTSET_FROM_CURSOR(_cursor_) to return a RESULTSET and pass that RESULTSET to TABLE(...). SeeReturning a table for a cursor.

Declaring a RESULTSET¶

You can declare a RESULTSET in the DECLARE section of a block or in theBEGIN … 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:

:= [ ASYNC ] ( ) ;

Where:

_resultsetname_

The name of the RESULTSET.

The name must be unique within the current scope.

The name must follow the naming rules for Object identifiers.

ASYNC

Runs the query as an asynchronous child job.

_query_

The query to assign to the RESULTSET.

To assign a query to a RESULTSET:

DECLARE res RESULTSET; BEGIN res := (SELECT col1 FROM mytable ORDER BY col1); ...

To assign a query to a RESULTSET and run the query as an asynchronous child job:

DECLARE res RESULTSET; BEGIN res := ASYNC (SELECT col1 FROM mytable ORDER BY col1); ...

To build a SQL string dynamically for the query, set _query_ to(EXECUTE IMMEDIATE _stringofsql_). 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;

Although you can set _query_ to an EXECUTE IMMEDIATE statement for a RESULTSET, you can’t 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
• Returning a RESULTSET as a table

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;

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 tofetch 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 withtable 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(_resultsetname_) syntax is supported only in theRETURN statement.

Even if you have used a cursor to fetch rows from the RESULTSET, the table returned by TABLE(_resultsetname_) 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 can’t 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
• Example: Returning a table from a stored procedure
• Example: Constructing the SQL statement dynamically
• Example: Declaring a RESULTSET variable without a DEFAULT clause
• Example: Using a CURSOR with a RESULTSET
• Additional examples that use a RESULTSET

For examples that use the ASYNC keyword to run queries specified for RESULTSETs as asynchronous child jobs, see Examples of using asynchronous child jobs.

Setting up the data for the examples¶

Many of the examples below use the table and data shown below:

CREATE OR REPLACE 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 use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE test_sp() RETURNS TABLE(a INTEGER) LANGUAGE SQL AS DECLAREresRESULTSETdefault(SELECTaFROMt001ORDERBYa);BEGINRETURNTABLE(res);END;DECLARE res RESULTSET default (SELECT a FROM t001 ORDER BY a); BEGIN RETURN TABLE(res); END;DECLAREresRESULTSETdefault(SELECTaFROMt001ORDERBYa);BEGINRETURNTABLE(res);END;;

Call the stored procedure:

+---+

You can also 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;

+---+

Example: Constructing the SQL statement dynamically¶

You can construct the SQL dynamically. The following is an example that executes the same query as the previous stored procedure 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 IDENTIFIER(?) ORDER BY a;'; BEGIN res := (EXECUTE IMMEDIATE :query USING(table_name)); RETURN TABLE(res); END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR) RETURNS TABLE(a INTEGER) LANGUAGE SQL AS DECLAREresRESULTSET;queryVARCHARDEFAULT′SELECTaFROMIDENTIFIER(?)ORDERBYa;′;BEGINres:=(EXECUTEIMMEDIATE:queryUSING(tablename));RETURNTABLE(res);ENDDECLARE res RESULTSET; query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;'; BEGIN res := (EXECUTE IMMEDIATE :query USING(table_name)); RETURN TABLE(res); ENDDECLAREresRESULTSET;queryVARCHARDEFAULT′SELECTaFROMIDENTIFIER(?)ORDERBYa;′;BEGINres:=(EXECUTEIMMEDIATE:queryUSING(tablen​ame));RETURNTABLE(res);END ;

To run the example, call the stored procedure and pass in the table name:

CALL test_sp_dynamic('t001');

+---+

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 use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE test_sp_02() RETURNS TABLE(a INTEGER) LANGUAGE SQL AS DECLAREresRESULTSET;BEGINres:=(SELECTaFROMt001ORDERBYa);RETURNTABLE(res);END;DECLARE res RESULTSET; BEGIN res := (SELECT a FROM t001 ORDER BY a); RETURN TABLE(res); END;DECLAREresRESULTSET;BEGINres:=(SELECTaFROMt001ORDERBYa);RETURNTABLE(res);END;;

To run the example, call the stored procedure:

+---+

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 use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE test_sp_03() RETURNS INTEGER LANGUAGE SQL AS DECLAREaccumulatorINTEGERDEFAULT0;res1RESULTSETDEFAULT(SELECTaFROMt001ORDERBYa);cur1CURSORFORres1;BEGINFORrowvariableINcur1DOaccumulator:=accumulator+rowvariable.a;ENDFOR;RETURNaccumulator;END;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;DECLAREaccumulatorINTEGERDEFAULT0;res1RESULTSETDEFAULT(SELECTaFROMt001ORDERBYa);cur1CURSORFORres1;BEGINFORrowv​ariableINcur1DOaccumulator:=accumulator+rowv​ariable.a;ENDFOR;RETURNaccumulator;END;;

Call the stored procedure, and the results add the values for a in the table (1 + 2):

+------------+

Additional examples that use a RESULTSET¶

Here are additional examples that use a RESULTSET:

• Use a RESULTSET-based FOR loop
  This example shows you how to use a FOR loop that iterates over a RESULTSET.
• Return a table for a cursor
  This example shows you how to use a cursor to return a table of data in a RESULTSET.
• Update table data with user input
  This example shows you how to use bind variables based on user input to update data in a table. It uses a FOR loop with conditional logic to iterate over the rows in a RESULTSET.
• Filter and collect data
  This example shows you how to use a RESULTSET to collect data and insert that data into a table to track historical trends.