Categories:

Table Functions

RESULT_SCAN

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table. This is particularly useful if you want to process the output from any of the following:

The command/query can be from the current session or any of your other sessions, including past sessions, as long as the 24 hour period has not elapsed. This period is not adjustable. For more details, see Using Persisted Query Results.

See also:

DESCRIBE RESULT (Account & Session DDL)

Syntax

RESULT_SCAN ( { '<query_id>'  | LAST_QUERY_ID() } )

Arguments

query_id or LAST_QUERY_ID()

Either the ID for a query you executed (within the last 24 hours in any session) or the LAST_QUERY_ID function, which returns the ID for a query within your current session.

Usage Notes

  • Snowflake stores all query results for 24 hours. This function only returns results for queries executed within this time period.

  • Result sets do not have any metadata associated with them, so processing large results might be slower than if you were querying an actual table.

  • The query containing the RESULT_SCAN can include clauses, such as filters and ORDER BY clauses, that were not in the original query. This allows you to narrow down or modify the result set.

  • A RESULT_SCAN is not guaranteed to return rows in the same order as the original query returned the rows. You can include an ORDER BY clause with the RESULT_SCAN to specify a specific order.

  • To retrieve the ID for a specific query, use any of the following methods:

    Web interface

    In either of the following locations, click the provided link to display/copy the ID:

    • In Worksheets Worksheet tab, after executing a query, the results includes a link for the ID.

    • In History History tab, each query includes the ID as a link.

    SQL

    Execute either of the following functions:

  • Only the user who ran the original query can use the RESULT_SCAN function to post-process the results of that query. Even another user with ACCOUNTADMIN privilege cannot access the results of another user’s query by calling RESULT_SCAN.

Collation Details

When RESULT_SCAN returns the results of the previous statement, RESULT_SCAN preserves the collation specification(s) of the values that it returns.

Examples

Simple Examples

Retrieve all values greater than 1 from the result of your most recent query in the current session:

SELECT $1 AS value FROM VALUES (1), (2), (3);

+-------+
| VALUE |
|-------|
|     1 |
|     2 |
|     3 |
+-------+

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE value > 1;

+-------+
| VALUE |
|-------|
|     2 |
|     3 |
+-------+

Retrieve all values from your second most recent query in the current session:

SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID(-2)));

Retrieve all values from your first query in the current session:

SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID(1)));

Retrieve the values from the c2 column in the result of the specified query:

SELECT c2 FROM TABLE(RESULT_SCAN('ce6687a4-331b-4a57-a061-02b2b0f0c17c'));

Examples using DESCRIBE and SHOW Commands

Process the result of a DESCRIBE USER command to retrieve particular fields of interest, for example the user’s default role. Note that because the output column names from the DESC USER command were generated in lowercase, the commands use delimited identifier notation (double quotes) around the column names in the query to ensure that the column names in the query match the column names in the output that was scanned.

DESC USER jessicajones;
SELECT "property", "value" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  WHERE "property" = 'DEFAULT_ROLE'
  ;

Process the result of a SHOW TABLES command to extract empty tables that are older than 21 days. The SHOW command generates lower-case column names, so the command quotes the names and use matching case:

SHOW TABLES;
-- Show the tables that are more than 21 days old and that are empty
-- (i.e. tables that I might have forgotten about).
SELECT "database_name", "schema_name", "name" as "table_name", "rows", "created_on"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE "rows" = 0 AND "created_on" < DATEADD(day, -21, CURRENT_TIMESTAMP())
    ORDER BY "created_on";

Process the result of a SHOW TABLES command to extract the tables in descending order of size. This example also illustrates using a UDF to show table size in a slightly more human-readable format:

-- Show byte counts with suffixes such as "KB", "MB", and "GB".
CREATE OR REPLACE FUNCTION NiceBytes(NUMBER_OF_BYTES FLOAT)
RETURNS VARCHAR
AS
$$
CASE
    WHEN NUMBER_OF_BYTES < 1024
        THEN NUMBER_OF_BYTES::VARCHAR
    WHEN NUMBER_OF_BYTES >= 1024 AND NUMBER_OF_BYTES < 1048576
        THEN (NUMBER_OF_BYTES / 1024)::VARCHAR || 'KB'
   WHEN NUMBER_OF_BYTES >= 1048576 AND NUMBER_OF_BYTES < (POW(2, 30))
       THEN (NUMBER_OF_BYTES / 1048576)::VARCHAR || 'MB'
    ELSE
        (NUMBER_OF_BYTES / POW(2, 30))::VARCHAR || 'GB'
END
$$
;
SHOW TABLES;
-- Show all of my tables in descending order of size.
SELECT "database_name", "schema_name", "name" as "table_name", NiceBytes("rows") AS "size"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY "size" DESC;

Examples Using a Stored Procedure

Stored procedure calls return a value. However, this value cannot be processed directly because you cannot embed a stored procedure call in another statement. To work around this limitation, you can use RESULT_SCAN to process the value returned by a stored procedure. A simplified example is below:

First, create a procedure that returns a “complicated” value (in this case, a string that contains JSON-compatible data) that can be processed after it has been returned from the CALL.

CREATE OR REPLACE PROCEDURE return_JSON()
    RETURNS VARCHAR
    LANGUAGE JavaScript
    AS
    $$
        return '{"keyA": "ValueA", "keyB": "ValueB"}';
    $$
    ;

Second, call the procedure:

CALL return_JSON();
+--------------------------------------+
| RETURN_JSON                          |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+

The next three steps extract the data from the result set.

Get the first (and only) column:

SELECT $1 AS output_col FROM table(RESULT_SCAN(LAST_QUERY_ID()));
+--------------------------------------+
| OUTPUT_COL                           |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+

Convert the output from a VARCHAR to a VARIANT:

SELECT PARSE_JSON(output_col) AS JSON_COL FROM table(RESULT_SCAN(LAST_QUERY_ID()));
+---------------------+
| JSON_COL            |
|---------------------|
| {                   |
|   "keyA": "ValueA", |
|   "keyB": "ValueB"  |
| }                   |
+---------------------+

Extract the value that corresponds to the key “keyB”:

SELECT JSON_COL:keyB FROM table(RESULT_SCAN(LAST_QUERY_ID()));
+---------------+
| JSON_COL:KEYB |
|---------------|
| "ValueB"      |
+---------------+

Here is a more compact way to extract the same data that was extracted in the previous example. This example has fewer statements, but is harder to read:

CALL return_JSON();
+--------------------------------------+
| RETURN_JSON                          |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
SELECT JSON_COL:keyB 
   FROM (
        SELECT PARSE_JSON($1::VARIANT) AS JSON_COL 
            FROM table(RESULT_SCAN(LAST_QUERY_ID()))
        );
+---------------+
| JSON_COL:KEYB |
|---------------|
| "ValueB"      |
+---------------+

The output from the CALL uses the function name as the column name, e.g.:

+--------------------------------------+
|              RETURN_JSON             |
+--------------------------------------+
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+

We can use that column name in the query. Here’s one additional compact version, in which the column is referenceed by name rather than the column number:

CALL return_JSON();
+--------------------------------------+
| RETURN_JSON                          |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
SELECT JSON_COL:keyB
        FROM (
             SELECT PARSE_JSON(RETURN_JSON::VARIANT) AS JSON_COL 
                 FROM table(RESULT_SCAN(LAST_QUERY_ID()))
             );
+---------------+
| JSON_COL:KEYB |
|---------------|
| "ValueB"      |
+---------------+