CREATE MACRO-Anweisung

Die CREATE MACRO-Anweisung definiert einen benannten, aufrufbaren SQL-Ausdruck als Datenbankschemaobjekt.

Makros können Parameter deklarieren, die von ihrem Ausdruck referenziert werden können. Sobald ein Makro angelegt ist, kann es durch Angabe seines Namens und das Übergeben von Werten an seine Parameter aufgerufen werden; dadurch wird sein Ausdruck ausgewertet und ein Wert geliefert. Je nach Makrotyp kann der Wert skalar oder ein TABLE-Wert sein. Die CREATE FUNCTION-Anweisung ist ein Alias für CREATE MACRO.

Die vereinfachte Syntax zum Anlegen eines Makros ist:

CREATE [OR REPLACE] [TEMPORARY] MACRO [IF NOT EXISTS] ⟨identifier⟩( [⟨parameters⟩] ) AS [TABLE] ⟨expression⟩;

• Der Bezeichner besteht aus dem Namen des Makros, der ein beliebiger gültiger SQL-Bezeichner sein kann. Ein Makro kann explizit mit einem vorhandenen Datenbankschema qualifiziert werden. Wird ein Schema angegeben, erscheint es auf die übliche Weise: vor dem Namen, mit einem Punkt zwischen Schema- und Makroname. Wird es nicht explizit angegeben, wird das Makro dem aktuellen Schema zugeordnet.
• Bei Verbindung mit einer persistenten Datenbankdatei wird das Makro in der Datenbank gespeichert. Das optionale Schlüsselwort TEMPORARY gibt an, dass das Makro nicht persistiert werden soll.
• Eine OR REPLACE-Klausel kann unmittelbar nach dem Schlüsselwort CREATE stehen und bewirkt, dass ein vorhandenes Makro gleichen Namens (innerhalb des Schemas) überschrieben wird. Ohne OR REPLACE schlägt ein solcher Versuch mit dem Fehler Macro Function already exists fehl.
• Eine IF NOT EXISTS-Klausel kann unmittelbar vor dem Bezeichner stehen und bewirkt, dass das Makro nur angelegt wird, wenn es noch nicht existiert. Entweder die Klausel OR REPLACE oder IF NOT EXISTS darf vorhanden sein, aber nicht beide.
• Dem Makronamen folgen Klammern. Hat das Makro Parameter, müssen diese innerhalb der Klammern deklariert werden.
• Das Schlüsselwort AS erscheint nach der schließenden Klammer, aber vor dem Ausdruck
• Das Schlüsselwort TABLE kann direkt nach dem Schlüsselwort AS und unmittelbar vor dem Ausdruck stehen und gibt an, dass das Makro ein Tabellenmakro ist und eine Ergebnismenge zurückgibt. Wird es weggelassen, ist das Makro automatisch ein Skalarmakro.
• Der Ausdruck kann jeder gültige SQL-Ausdruck sein, sofern der Typ des Ausdrucks mit dem Typ des Makros übereinstimmt.

Bitte sehen Sie sich das Syntaxdiagramm für einen präziseren und detaillierteren Überblick über die CREATE MACRO-Anweisung an.

Arten von Makros

Die Kontexte, in denen ein bestimmtes Makro aufgerufen werden kann, hängen vom Datentyp seines Ergebniswerts ab:

• Skalarmakros werten zu einem skalaren Wert aus. Bei Skalarmakros kann der Ausdruck entweder ein einfacher Ausdruck oder eine skalare Unterabfrage sein.
• Tabellenmakros geben ein tabellarisches Ergebnis zurück: Beim Aufruf wirken sie im Wesentlichen als Tabellenfunktionen und geben einen Tabellenwert zurück. Ihr Ausdruck kann eine SELECT-Anweisung oder ein Aufruf einer anderen Tabellenfunktion sein.

Parameter deklarieren

Makros können Parameter deklarieren. Die Parameterdeklarationen erscheinen als kommagetrennte Liste zwischen den Klammern vor dem Schlüsselwort AS. Die vereinfachte Syntax für eine einzelne Parameterdeklaration ist:

⟨parameter-name⟩ [⟨datatype⟩] [ := ⟨default-value⟩ ]

• Der Parametername ist verpflichtend und kann ein beliebiger gültiger SQL-Bezeichner sein. Parameternamen müssen innerhalb der Parameterliste eindeutig sein. Der Versuch, mehrere Parameter mit demselben Namen zu definieren, führt zum Fehler Duplicate parameter.
• Optional kann ein Parameter explizit einen bestimmten Datentyp angeben. Das kann jeder der vorhandenen DuckDB-Datentypen sein. Hinweis: Es gibt keine Möglichkeit, einen Parameter vom Typ TABLE anzugeben.
• Ein Parameter kann optional einen Standardwert angeben. Das geschieht mit dem Zuweisungsoperator :=, gefolgt von dem Ausdruck, der als Standardwert verwendet werden soll.
• Der Standardwertausdruck wird grundsätzlich zur Definitionszeit ausgewertet – NICHT zur Laufzeit. (Es gibt einige Ausnahmen wie CURRENT_SCHEMA. Verlassen Sie sich darauf aber nicht: Wenn ein Standardwert dynamisch sein soll, verwenden Sie einen bekannten Wert wie NULL als Default und bedingte Logik im Ausdruck, um den Laufzeitwert zu erzeugen).
• Die Angabe eines Standardwertausdrucks macht den Parameter effektiv optional: Beim Aufruf des Makros findet der DuckDB-Binder Kandidatensignaturen anhand der übergebenen Parameter, ergänzt um Signaturen, die Standardwerte für fehlende Parameter angeben.
• Ein Parameter mit Standardwert darf nicht vor der Definition eines Parameters ohne Standardwert erscheinen. Mit anderen Worten: Parameter ohne Standardwerte müssen „vorne“ definiert werden; Parameter mit Standardwerten erscheinen „hinten“.
• Mehrere Parameterdeklarationen werden durch ein Komma voneinander getrennt.

Überladen

Makros unterstützen Überladen:

• Eine einzelne CREATE MACRO-Anweisung kann mehrere Implementierungen definieren (manchmal „Overloads“ genannt), jede mit eigener Parameterliste, eigenem Schlüsselwort AS und eigenem Ausdruck. Beachten Sie, dass alle Implementierungen in derselben CREATE MACRO-Anweisung definiert werden: Es ist nicht möglich, einzelne Implementierungen nach dem Anlegen des Makros hinzuzufügen, zu entfernen oder zu ändern.
• Mehrere Implementierungen werden durch ein Komma voneinander getrennt.
• Jede Implementierung muss eine eindeutige Parameter-Typ-Signatur haben: Das heißt, in einer CREATE MACRO-Anweisung müssen alle Implementierungen mit derselben Parameteranzahl jeweils eine eindeutige Folge von Parametertypen haben – unabhängig von den Parameternamen. Ist eine Parameter-Typ-Signatur nicht eindeutig, führt das zum Fehler Ambiguity in macro overloads.
• Überladen gilt nur für die Parametertypen, nicht für den Makrotyp selbst: Bei einem einzelnen Makro sind alle Implementierungen entweder skalar oder TABLE.
• Beim Überladen von Tabellenfunktionen, die mit einer SELECT-Anweisung als Ausdruck definiert sind, müssen Sie die SELECT-Anweisung wahrscheinlich in Klammern setzen.

Makros aufrufen

Makros werden durch Nennung ihres Namens, gefolgt von Klammern, aufgerufen. Zwischen den Klammern kann eine kommagetrennte Liste von Wertausdrücken stehen – das sind die tatsächlichen Parameter. Der DuckDB-Binder prüft die Datentypen der tatsächlichen Parameter und versucht, eine Implementierung des Makros mit passender Signatur zu finden. Wird eine Implementierung gefunden, werden die Parameterwerte übergeben und der Ausdruck der Implementierung ausgewertet; schließlich wird der resultierende Wert zurückgegeben und an der Stelle verwendet, an der das Makro aufgerufen wurde. Das ähnelt dem Aufruf einer Funktion.

Im Allgemeinen ist ein Makroaufruf gültig, wenn sein Ausdruck in diesem Kontext ebenfalls erscheinen könnte:

• Ein Aufruf eines Skalarmakros kann in der SELECT-Klausel oder in der WHERE-Klausel einer SELECT-Anweisung verwendet werden.
• Referenziert der Ausdruck eines Skalarmakros eine Aggregatfunktion, verhält sich das Makro ebenfalls wie eine Aggregatfunktion.
• Ein Aufruf eines Tabellenmakros kann in der FROM-Klausel einer SELECT-Anweisung oder in einer CALL-Anweisung erscheinen.

Parameter übergeben

Parameterwerte können als kommagetrennte Liste von Ausdrücken zwischen den Klammern nach dem Makronamen an das Makro übergeben werden.

Parameterwerte können positionsbasiert oder namentlich übergeben werden.

• Positionsbasiertes Übergeben bedeutet, dass nur der Wertausdruck übergeben wird;
• Im Gegensatz zum positionsbasierten Übergeben weist das namentliche Übergeben den tatsächlichen Parameterwert explizit einem bestimmten formalen Parameter zu. Das geschieht durch Nennung des Parameternamens, gefolgt vom Zuweisungsoperator := und dem Parameterwertausdruck.
• Beachten Sie, dass einige eingebaute Funktionen ebenfalls benannte Parameter erlauben, aber = als Zuweisungsoperator zulassen. Bei Makros funktioniert das nicht! Stattdessen wird = als Vergleichsoperator interpretiert. Das macht den – beabsichtigten – benannten Parameter effektiv zu einem positionsbasierten Parameter, der das Ergebnis des Vergleichs von Parametername und Parameterwertausdruck übergibt.
• Enthält ein Makroaufruf benannte Parameter, müssen diese nach allen positionsbasierten Parametern erscheinen. Mit anderen Worten: Alle positionsbasierten Parameter müssen „vorne“ stehen; alle benannten Parameter müssen „hinten“ stehen.
• Positionsbasierte Parameter werden definitionsgemäß in derselben Reihenfolge übergeben, in der sie deklariert wurden. Benannte Parameter können in beliebiger Reihenfolge erscheinen (sofern sie nach den positionsbasierten Parametern stehen).

Beispiele

Skalarmakros

Ein Makro anlegen, das zwei Ausdrücke (a und b) addiert:

CREATE MACRO add(a, b) AS a + b;

Ein Makro anlegen und mögliche vorhandene Definitionen ersetzen:

CREATE OR REPLACE MACRO add(a, b) AS a + b;

Ein Makro anlegen, falls es noch nicht existiert, sonst nichts tun:

CREATE MACRO IF NOT EXISTS add(a, b) AS a + b;

Ein Makro für einen CASE-Ausdruck anlegen:

CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END;

Ein Makro anlegen, das eine Unterabfrage ausführt:

CREATE MACRO one() AS (SELECT 1);

Makros sind schemaabhängig und haben einen Alias, FUNCTION:

CREATE FUNCTION main.my_avg(x) AS sum(x) / count(x);

Ein Makro mit einem Standardparameter anlegen:

CREATE MACRO add_default(a, b := 5) AS a + b;

Ein Makro arr_append anlegen (mit zu array_append äquivalenter Funktionalität):

CREATE MACRO arr_append(l, e) AS list_concat(l, list_value(e));

Ein Makro mit einem typisierten Parameter anlegen:

CREATE MACRO is_maximal(a INTEGER) AS a = 2^31 - 1;

Tabellenmakros

Ein Tabellenmakro ohne Parameter anlegen:

CREATE MACRO static_table() AS TABLE
    SELECT 'Hello' AS column1, 'World' AS column2;

Ein Tabellenmakro mit Parametern (die beliebigen Typs sein können) anlegen:

CREATE MACRO dynamic_table(col1_value, col2_value) AS TABLE
    SELECT col1_value AS column1, col2_value AS column2;

Ein Tabellenmakro anlegen, das mehrere Zeilen zurückgibt. Es wird ersetzt, falls es bereits existiert, und es ist temporär (wird automatisch gelöscht, wenn die Verbindung endet):

CREATE OR REPLACE TEMP MACRO dynamic_table(col1_value, col2_value) AS TABLE
    SELECT col1_value AS column1, col2_value AS column2
    UNION ALL
    SELECT 'Hello' AS col1_value, 456 AS col2_value;

Ein Argument als Liste übergeben:

CREATE MACRO get_users(i) AS TABLE
    SELECT * FROM users WHERE uid IN (SELECT unnest(i));

Ein Beispiel für die Verwendung des Tabellenmakros get_users ist das folgende:

CREATE TABLE users AS
    SELECT *
    FROM (VALUES (1, 'Ada'), (2, 'Bob'), (3, 'Carl'), (4, 'Dan'), (5, 'Eve')) t(uid, name);
SELECT * FROM get_users([1, 5]);

Um Makros auf beliebigen Tabellen zu definieren, verwenden Sie die Funktion query_table. Das folgende Makro berechnet zum Beispiel eine spaltenweise Prüfsumme einer Tabelle:

CREATE MACRO checksum(tbl) AS TABLE
    SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
    FROM query_table(tbl);

CREATE TABLE tbl AS SELECT unnest([42, 43]) AS x, 100 AS y;
SELECT * FROM checksum('tbl');

Überladen

Es ist möglich, ein Makro anhand der Typen oder der Anzahl seiner Parameter zu überladen; das funktioniert sowohl für Skalar- als auch für Tabellenmakros.

Durch Überladen können wir sowohl add_x(a, b) als auch add_x(a, b, c) mit unterschiedlichen Funktionskörpern haben.

CREATE MACRO add_x
    (a, b) AS a + b,
    (a, b, c) AS a + b + c;

SELECT
    add_x(21, 42) AS two_args,
    add_x(21, 42, 21) AS three_args;

two_args	three_args
63	84

CREATE OR REPLACE MACRO is_maximal
    (a TINYINT) AS a = 2^7 - 1,
    (a INT) AS a = 2^31 - 1;

SELECT
    is_maximal(127::TINYINT) AS tiny,
    is_maximal(127) AS regular;

tiny	regular
true	false

Syntax

Makros erlauben es, Abkürzungen für Kombinationen von Ausdrücken anzulegen.

CREATE MACRO add(a) AS a + b;

Binder Error:
Referenced column "b" not found in FROM clause!

Das funktioniert:

CREATE MACRO add(a, b) AS a + b;

Verwendungsbeispiel:

SELECT add(1, 2) AS x;

x
3

Das schlägt jedoch fehl:

SELECT add('hello', 3);

Binder Error:
Could not choose a best candidate function for the function call "add(STRING_LITERAL, INTEGER_LITERAL)". In order to select one, please add explicit type casts.
    Candidate functions:
    add(DATE, INTEGER) -> DATE
    add(INTEGER, INTEGER) -> INTEGER

Makros können Standardparameter haben.

b ist ein Standardparameter:

CREATE MACRO add_default(a, b := 5) AS a + b;

Das Folgende ergibt 42:

SELECT add_default(37);

Die Reihenfolge benannter Parameter spielt keine Rolle:

CREATE MACRO triple_add(a, b := 5, c := 10) AS a + b + c;

SELECT triple_add(40, c := 1, b := 1) AS x;

x
42

Wenn Makros verwendet werden, werden sie expandiert (d. h. durch den ursprünglichen Ausdruck ersetzt), und die Parameter innerhalb des expandierten Ausdrucks werden durch die übergebenen Argumente ersetzt. Schritt für Schritt:

Das oben definierte Makro add wird in einer Abfrage verwendet:

SELECT add(40, 2) AS x;

Intern wird add durch seine Definition a + b ersetzt:

SELECT a + b AS x;

Dann werden die Parameter durch die übergebenen Argumente ersetzt:

SELECT 40 + 2 AS x;

Einschränkungen

Unterabfrage-Makros verwenden

Tabellenmakros sowie Skalarmakros, die mit skalaren Unterabfragen definiert sind, können nicht in den Argumenten von Tabellenfunktionen verwendet werden. DuckDB gibt den folgenden Fehler zurück:

Binder Error:
Table function cannot contain subqueries

Überladen

Überladen für Makrofunktionen müssen beim Anlegen gesetzt werden; es ist nicht möglich, ein Makro mit demselben Namen zweimal zu definieren, ohne zuerst die erste Definition zu entfernen.

Rekursive Funktionen

Das Definieren rekursiver Funktionen wird nicht unterstützt. Das folgende Makro – das die n-te Zahl der Fibonacci-Folge berechnen soll – schlägt zum Beispiel fehl:

CREATE OR REPLACE FUNCTION fibo(n) AS (SELECT 1);
CREATE OR REPLACE FUNCTION fibo(n) AS (
    CASE
        WHEN n <= 1 THEN 1
        ELSE fibo(n - 1)
    END
);
SELECT fibo(3);

Binder Error:
Max expression depth limit of 1000 exceeded. Use "SET max_expression_depth TO x" to increase the maximum expression depth.

Function Chaining auf der ersten Funktion funktioniert nicht

Makros unterstützen den Punktoperator für Function Chaining auf der ersten Funktion nicht. Zur Veranschaulichung ein Beispiel mit der Funktion lower, das funktioniert:

CREATE OR REPLACE MACRO low(s) AS lower(s);
SELECT low('AA');

Das Umschreiben von lower(s) auf Function Chaining funktioniert jedoch nicht:

CREATE OR REPLACE MACRO low(s) AS s.lower();
SELECT low('AA');

Binder Error:
Referenced column "s" not found in FROM clause!

Die Liste der Makros und Tabellenmakros anzeigen

Sie können die folgende Abfrage verwenden, um die Liste der Makros und Tabellenmakros anzuzeigen:

SELECT schema_name, function_name, function_type, parameters
FROM duckdb_functions();