Die Funktionen query und query_table

Die Funktionen query_table und query ermöglichen leistungsfähiges und dynamischeres SQL.

Die Funktion query_table gibt die Tabelle zurück, deren Name durch das String-Argument angegeben wird; die Funktion query gibt die Tabelle zurück, die durch Ausführen der im String-Argument angegebenen Abfrage entsteht.

Beide Funktionen akzeptieren nur konstante Strings. Sie erlauben beispielsweise, einen Tabellennamen als Parameter eines Prepared Statements zu übergeben:

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

In Kombination mit dem COLUMNS-Ausdruck lassen sich sehr generische, rein SQL-basierte Makros schreiben. Unten steht beispielsweise eine eigene Version von SUMMARIZE, die min und max jeder Spalte einer Tabelle berechnet:

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://blobs.duckdb.org/data/ontime.parquet')
LIMIT 3;

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

Die Funktion query erlaubt noch mehr Flexibilität. Benutzer, die die stack-Syntax von pandas der SQL-Syntax UNPIVOT vorziehen, können beispielsweise Folgendes verwenden:

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