query and query_table Functions

The query_table and query functions enable powerful and more dynamic SQL.

The query_table function returns the table whose name is specified by its string argument; the query function returns the table obtained by executing the query specified by its string argument.

Both functions only accept constant strings. For example, they allow passing in a table name as a prepared statement parameter:

CREATE TABLE my_table (i INTEGER);
INSERT INTO my_table VALUES (42);

PREPARE select_from_table AS SELECT * FROM query_table($1);
EXECUTE select_from_table('my_table');

i
42

When combined with the COLUMNS expression, we can write very generic SQL-only macros. For example, below is a custom version of SUMMARIZE that computes the min and max of every column in a table:

CREATE OR REPLACE MACRO my_summarize(table_name) AS TABLE
SELECT
    unnest([*COLUMNS('alias_.*')]) AS column_name,
    unnest([*COLUMNS('min_.*')]) AS min_value,
    unnest([*COLUMNS('max_.*')]) AS max_value
FROM (
    SELECT
        any_value(alias(COLUMNS(*))) AS "alias_\0",
        min(COLUMNS(*))::VARCHAR AS "min_\0",
        max(COLUMNS(*))::VARCHAR AS "max_\0"
    FROM query_table(table_name::VARCHAR)
);

SELECT *
FROM my_summarize('https://${uri}/ontime.parquet')
LIMIT 3;

column_name	min_value	max_value
year	2017	2017
quarter	1	3
month	1	9

The query function allows for even more flexibility. For example, users who prefer pandas' stack syntax over SQL's UNPIVOT syntax, may use:

CREATE OR REPLACE MACRO stack(table_name, index, name, values) AS TABLE 
FROM query(
    'UNPIVOT ' || table_name 
    || ' ON COLUMNS(* EXCLUDE (' || array_to_string(index, ', ') 
    || ')) INTO NAME ' || name || ' VALUES ' || values
);

WITH cities AS (
    FROM (
        VALUES 
            ('NL', 'Amsterdam', '10', '12', '15'),
            ('US', 'New York', '100', '120', '150')
    ) _(country, city, '2000', '2010', '2020')
)
SELECT *
FROM stack('cities', ['country', 'city'], 'year', 'population');

country	city	year	population
NL	Amsterdam	2000	10
NL	Amsterdam	2010	12
NL	Amsterdam	2020	15
US	New York	2000	100
US	New York	2010	120
US	New York	2020	150