RESULT_SCAN¶

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 |
+-------+

Copy

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

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

Copy

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

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

Copy

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'));

Copy

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'
  ;

Copy

Process the result of a SHOW TABLES command to extract empty tables that are older than 21 days. The SHOW command generates lowercase 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";

Copy

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 INTEGER)
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("bytes") AS "size"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY "bytes" DESC;

Copy

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"}';
    $$
    ;

Copy

Second, call the procedure:

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

Copy

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"} |
+--------------------------------------+

Copy

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"  |
| }                   |
+---------------------+

Copy

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

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

Copy

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"      |
+---------------+

Copy

The output from the CALL uses the function name as the column name. For example:

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

Copy

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"      |
+---------------+

Copy

Example with duplicate column names¶

The following example shows that RESULT_SCAN effectively references alternate column names when there are duplicate column names in the original query:

Create two tables that have at least one column with the same name:

CREATE TABLE employees (id INT);

Copy

CREATE TABLE dependents (id INT, employee_id INT);

Copy

Load data into the two tables:

INSERT INTO employees (id) VALUES (11);

Copy

INSERT INTO dependents (id, employee_id) VALUES (101, 11);

Copy

Now execute a query for which the output will contain two columns with the same name:

SELECT * 
    FROM employees INNER JOIN dependents
        ON dependents.employee_ID = employees.id
    ORDER BY employees.id, dependents.id
    ;
+----+-----+-------------+
| ID |  ID | EMPLOYEE_ID |
|----+-----+-------------|
| 11 | 101 |          11 |
+----+-----+-------------+

Copy

Note that the output in Snowsight is different than the output shown above, since Snowsight handles duplicate column names automatically.

Now call RESULT_SCAN to process the results of that query. Note that if different columns that have the same name in the results, RESULT_SCAN uses the original name for the first column and assigns the second column a modified name that is unique. To make the name unique, RESULT_SCAN appends the suffix “_<n>” to the name, where “<n>” is the next number available that produces a name that is different from the names of the previous columns.

SELECT id, id_1, employee_id
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE id_1 = 101;
+----+------+-------------+
| ID | ID_1 | EMPLOYEE_ID |
|----+------+-------------|
| 11 |  101 |          11 |
+----+------+-------------+

Copy