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:

Query

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

Result

 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:

Query

CREATE OR REPLACE MACRO my_summarize(table_name) AS TABLESELECT    unnest([*COLUMNS('alias_.*')]) AS column_name,    unnest([*COLUMNS('min_.*')]) AS min_value,    unnest([*COLUMNS('max_.*')]) AS max_valueFROM (    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('ontime')LIMIT 3;

Result

 column_name | min_value | max_value-------------+-----------+----------- dep_delay   | -2        | 5 arr_delay   | -3        | 10 carrier     | AA        | UA

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

Query

CREATE OR REPLACE MACRO stack(table_name, index, name, values) AS TABLEFROM 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');

Result

 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