The CREATE MACRO statement can create a scalar or table macro (function) in the catalog.
A macro may only be a single SELECT statement (similar to a VIEW), but it has the benefit of accepting parameters.
For a scalar macro, CREATE MACRO is followed by the name of the macro, and optionally parameters within a set of parentheses. The keyword AS is next, followed by the text of the macro. By design, a scalar macro may only return a single value.
For a table macro, the syntax is similar to a scalar macro except AS is replaced with AS TABLE. A table macro may return a table of arbitrary size and shape.
If a
MACROis temporary, it is only usable within the same database connection and is deleted when the connection is closed.
Examples
Scalar Macros
Create a macro that adds two expressions (a and b):
CREATE MACRO add(a, b) AS a + b;
Create a macro for a case expression:
CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END;
Create a macro that does a subquery:
CREATE MACRO one() AS (SELECT 1);
Create a macro with a common table expression. Note that parameter names get priority over column names. To work around this, disambiguate using the table name.
CREATE MACRO plus_one(a) AS (WITH cte AS (SELECT 1 AS a) SELECT cte.a + a FROM cte);
Macros are schema-dependent, and have an alias, FUNCTION:
CREATE FUNCTION main.my_avg(x) AS sum(x) / count(x);
Create a macro with default constant parameters:
CREATE MACRO add_default(a, b := 5) AS a + b;
Create a macro arr_append (with a functionality equivalent to array_append):
CREATE MACRO arr_append(l, e) AS list_concat(l, list_value(e));
Table Macros
Create a table macro without parameters:
CREATE MACRO static_table() AS TABLE
SELECT 'Hello' AS column1, 'World' AS column2;
Create a table macro with parameters (that can be of any type):
CREATE MACRO dynamic_table(col1_value, col2_value) AS TABLE
SELECT col1_value AS column1, col2_value AS column2;
Create a table macro that returns multiple rows:
It will be replaced if it already exists, and it is temporary (will be automatically deleted when the connection ends):
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;
Pass an argument as a list: SELECT * FROM get_users([1, 5]):
CREATE MACRO get_users(i) AS TABLE
SELECT * FROM users WHERE uid IN (SELECT unnest(i));
Syntax
Macros allow you to create shortcuts for combinations of expressions.
CREATE MACRO add(a) AS a + b;
Binder Error: Referenced column "b" not found in FROM clause!
This works:
CREATE MACRO add(a, b) AS a + b;
Usage example:
SELECT add(1, 2) AS x;
| x |
|---|
| 3 |
However, this fails:
SELECT add('hello', 3);
Binder Error: Could not choose a best candidate function for the function call "+(STRING_LITERAL, INTEGER_LITERAL)". In order to select one, please add explicit type casts.
Candidate functions:
+(DATE, INTEGER) -> DATE
+(INTEGER, INTEGER) -> INTEGER
Macros can have default parameters. Unlike some languages, default parameters must be named when the macro is invoked.
b is a default parameter:
CREATE MACRO add_default(a, b := 5) AS a + b;
The following will result in 42:
SELECT add_default(37);
The following will throw an error:
SELECT add_default(40, 2);
Binder Error: Macro function 'add_default(a)' requires a single positional argument, but 2 positional arguments were provided.
Default parameters must used by assigning them like the following:
SELECT add_default(40, b := 2) AS x;
| x |
|---|
| 42 |
However, the following fails:
SELECT add_default(b := 2, 40);
Binder Error: Positional parameters cannot come after parameters with a default value!
The order of default parameters does not matter:
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 |
When macros are used, they are expanded (i.e., replaced with the original expression), and the parameters within the expanded expression are replaced with the supplied arguments. Step by step:
The add macro we defined above is used in a query:
SELECT add(40, 2) AS x;
Internally, add is replaced with its definition of a + b:
SELECT a + b; AS x
Then, the parameters are replaced by the supplied arguments:
SELECT 40 + 2 AS x;
Limitations
Using Named Parameters
Currently, positional macro parameters can only be used positionally, and named parameters can only be used by supplying their name. Therefore, the following will not work:
CREATE MACRO my_macro(a, b := 42) AS (a + b);
SELECT my_macro(32, 52);
Error: Binder Error: Macro function 'my_macro(a)' requires a single positional argument, but 2 positional arguments were provided.
Using Subquery Macros
If a MACRO is defined as a subquery, it cannot be invoked in a table function. DuckDB will return the following error:
Binder Error: Table function cannot contain subqueries