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

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, such as the user’s default role. 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 lowercase column names, so the command quotes the names to use matching case:

SHOW TABLES;
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. The following example also shows how to use 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;

Examples using a stored procedure¶

Stored procedure calls return a value. However, this value can’t be processed directly because you can’t 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"}';
  $$
  ;

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 value to a VARIANT value:

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

The following example shows 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. You can use that column name in the query. The following example shows one additional compact version, in which the column is referenced by name instead of 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"      |
+---------------+

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

CREATE TABLE dependents (id INT, employee_id INT);

Load data into the two tables:

INSERT INTO employees (id) VALUES (11);

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

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

Now call RESULT_SCAN to process the results of that query. 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 |
+----+------+-------------+