CREATE MACRO
The CREATE MACRO statement can create a scalar or table macro (function) in the catalog.
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.
Examples
Scalar Macros
Create a macro that adds two expressions (a and b):
CREATE MACRO add(a, b) AS a + b;Create a macro, replacing possible existing definitions:
CREATE OR REPLACE MACRO add(a, b) AS a + b;Create a macro if it does not already exist, else do nothing:
CREATE MACRO IF NOT EXISTS 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);Macros are schema-dependent, and have an alias, FUNCTION:
CREATE FUNCTION main.my_avg(x) AS sum(x) / count(x);Create a macro with a default parameter:
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));Create a macro with a typed parameter:
CREATE MACRO is_maximal(a INTEGER) AS a = 2^31 - 1;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:
CREATE MACRO get_users(i) AS TABLE SELECT * FROM users WHERE uid IN (SELECT unnest(i));An example for how to use the get_users table macro is the following:
SELECT * FROM get_users([1, 5]);uid | name -----+------ 1 | Ada 5 | Eve
To define macros on arbitrary tables, use the query_table function. For example, the following macro computes a column-wise checksum on a table:
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');x | y ----------------------------------------+--- 60609334165039584609948387465088153270 | 0
An argument that is a constant expression takes the place of its parameter as its value, so it can stand where a constant is required, such as in LIMIT. Any other argument that the query reads more than once, other than a column or a parameter, is evaluated once for each call.
SQL Functions
CREATE FUNCTION also takes PostgreSQL's form of an SQL function: typed parameters, a RETURNS clause, LANGUAGE sql and the body as a string. Any language other than sql is not supported. The function returns its RETURNS type, reached from the body's type the way PostgreSQL assigns a value to a column of that type. As in PostgreSQL, a type modifier of the result, such as the precision and scale of numeric(4,2), is not applied:
CREATE FUNCTION add_one(a int) RETURNS bigint LANGUAGE sql AS 'SELECT a + 1';
SELECT add_one(1) AS result, pg_typeof(add_one(1)) AS type; result | type
--------+--------
2 | bigintIn the body, a parameter is a value of the call: it can stand next to an aggregate, in a HAVING clause, in a WITH query or in a nested subquery, and an aggregate over a parameter aggregates the rows of the query it is written in:
CREATE FUNCTION count_below(n bigint) RETURNS bigint LANGUAGE sql AS 'SELECT count(*) * 100 + sum(n) + n FROM generate_series(1, 10) g WHERE g < n';
SELECT count_below(4) AS result; result
--------
316Inside a query of the body, a column of that query, or of a query of the body around it, takes precedence over a parameter of the same name. The parameter is then reached by qualifying it with the name of the function, as function_name.parameter, which also works anywhere else in the body. A parameter of a composite type gives its fields as parameter.field:
CREATE TABLE menu (item text, price int);
INSERT INTO menu VALUES ('tea', 3), ('cake', 5), ('pie', 4);
CREATE FUNCTION cheaper_than(price int) RETURNS SETOF text LANGUAGE sql AS 'SELECT item FROM menu WHERE price < cheaper_than.price ORDER BY item';
SELECT * FROM cheaper_than(5);cheaper_than -------------- pie tea
STRICT, or RETURNS NULL ON NULL INPUT, makes the function return NULL without running its body when any argument is NULL, and a set-returning function return no rows. CALLED ON NULL INPUT, the default, runs the body:
CREATE FUNCTION plus_one(x int) RETURNS int LANGUAGE sql STRICT AS 'SELECT coalesce(x, 0) + 1';
SELECT plus_one(NULL) AS for_null, plus_one(1) AS for_one;for_null | for_one ----------+--------- NULL | 2
A parameter takes a default after DEFAULT or =, as well as after :=. IMMUTABLE, STABLE and VOLATILE, volatile by default, are shown in pg_proc.provolatile and in the function's definition; what a call evaluates follows its body.
A function that RETURNS void runs its body to the end, every row of a query body included, and returns NULL. A function whose body modifies data cannot be called for each row of a query:
CREATE TABLE visits (who text);
CREATE FUNCTION visit(who text) RETURNS void LANGUAGE sql AS 'INSERT INTO visits VALUES (who)';
SELECT visit('ann') IS NULL AS returned_null;
SELECT who FROM visits;
SELECT visit(name)FROM (VALUES ('bob'), ('eve')) v(name);returned_null --------------- t who ----- ann db error: ERROR: A function whose body modifies data cannot be called for each row of a query
Without SETOF, the function returns the first row of the body, also when its result calls a set-returning function such as unnest. RETURNS SETOF <type> returns every row of the body. Called in FROM, the function's column is named after the function:
CREATE FUNCTION evens(n int) RETURNS SETOF int LANGUAGE sql AS 'SELECT g FROM generate_series(2, n, 2) g';
SELECT * FROM evens(6); evens
-------
2
4
6RETURNS TABLE names and types the columns:
CREATE FUNCTION numbered(n int) RETURNS TABLE (i int, label text) LANGUAGE sql AS 'SELECT g, ''#'' || g FROM generate_series(1, n) g';
SELECT * FROM numbered(2);i | label ---+------- 1 | #1 2 | #2
An OUT parameter is a column of the result and an INOUT parameter is both an argument and a column. A function with one output returns that type, one with several returns a record of them, and RETURNS SETOF with outputs returns every row. A RETURNS clause next to outputs must name that type: the output's type for one output, record for several. Called in FROM, a record function returns its outputs as columns:
CREATE FUNCTION split_name(full_name text, OUT first_name text, OUT last_name text) LANGUAGE sql AS $$SELECT split_part(full_name, ' ', 1), split_part(full_name, ' ', 2)$$;
SELECT * FROM split_name('Ada Lovelace');
SELECT split_name('Ada Lovelace') AS name;first_name | last_name ------------+----------- Ada | Lovelace name ---------------- (Ada,Lovelace)
Moving to Another Schema
ALTER FUNCTION, ALTER PROCEDURE or ALTER MACRO ... SET SCHEMA moves a function, procedure or macro, scalar or table, to another schema, with all its overloads:
CREATE FUNCTION times_two(a int) RETURNS int LANGUAGE sql AS 'SELECT a * 2';
ALTER FUNCTION times_two SET SCHEMA archive;
SELECT archive.times_two(21) AS n;n ---- 42
The move needs the CREATE privilege on the target schema, which must not already hold a function or macro of that name. Moving a function into the schema it is already in changes nothing. A function that another object depends on can't be moved.
Overloading
It is possible to overload a macro based on the types or the number of its parameters; this works for both scalar and table macros.
By providing overloads we can have both add_x(a, b) and add_x(a, b, c) with different function bodies.
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 | 84CREATE 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 ------+--------- t | f
Syntax
Macros allow you to create shortcuts for combinations of expressions.
CREATE MACRO add(a) AS a + b;db error: ERROR: Referenced column "b" was not found because the FROM clause is missing
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);db error: ERROR: Could not convert string 'hello' to INT32
Macros can have default parameters.
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); add_default
-------------
42The order of named 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;x ---- 42
Internally, add is replaced with its definition of a + b:
SELECT a + b AS x;db error: ERROR: Referenced column "a" was not found because the FROM clause is missing
Then, the parameters are replaced by the supplied arguments:
SELECT 40 + 2 AS x;x ---- 42
An argument that the expression uses more than once is evaluated once for each row, and only for the rows where the expression reads it, such as the taken branch of a CASE, including inside a lambda, where it is evaluated only for the elements that read it:
CREATE MACRO doubled_if_positive(d, x) AS CASE WHEN d > 0 THEN x + x END;
SELECT d, doubled_if_positive(d, 10 / d) AS resultFROM (VALUES (0), (2), (5)) t(d)ORDER BY d;d | result ---+-------- 0 | NULL 2 | 10 5 | 4
A volatile argument such as random() or nextval('s') is no exception: the expression sees one value of it for each row:
CREATE SEQUENCE ticket;
CREATE MACRO doubled(x) AS x + x;
SELECT doubled(nextval('ticket')) AS resultFROM range(3)ORDER BY result; result
--------
2
4
6A macro used as a predicate in WHERE or in a join condition is planned like the expression it expands to, so it can filter a table scan, use an index or make a hash join.
In a macro whose body contains a subquery, an aggregate over a parameter aggregates the rows of the subquery it is written in:
CREATE MACRO four_times(x) AS (SELECT sum(x) FROM range(4) r(v));
SELECT i, four_times(i) AS resultFROM range(3) t(i)ORDER BY i;i | result ---+-------- 0 | 0 1 | 4 2 | 8
An argument is evaluated in the calling query: its names are the calling query's columns, an aggregate or window function in it is computed over the rows of the calling query, and a nested call of a macro with a subquery is evaluated once for each row of the calling query, even when the subquery returns no rows. Only an argument that names something the calling query does not have is replaced as written, so that the body can resolve it, such as a column of the table the body reads with query_table.
A macro or function whose body is a query or contains a subquery runs that query for each call, so a volatile argument, or a volatile function in the body, gives every row its own value:
CREATE SEQUENCE counter;
CREATE FUNCTION twice(x bigint) RETURNS bigint LANGUAGE sql AS 'SELECT x + x FROM (VALUES (1)) v(one)';
SELECT twice(nextval('counter')) AS resultFROM range(3)ORDER BY result; result
--------
2
4
6Limitations
Using Subquery Macros
Table macros as well as scalar macros defined using scalar subqueries cannot be used in the arguments of table functions. SereneDB will return an error:
SELECT * FROM range(scalar_sub());db error: ERROR: Table function cannot contain subqueries
A scalar subquery made of a single expression, with no FROM or other clause, such as (SELECT 5), expands to that expression, so a macro defined with it has no such limitation, unless the expression calls an aggregate, window or set-returning function or contains a subquery. The same limitation keeps a macro or function whose body is a query, such as one that reads a table, out of CHECK constraints, DEFAULT values, generated columns and index expressions.
Recursive Functions
Defining recursive functions is not supported. A recursive macro expands until the expansion runs out of stack, and the statement fails with stack depth limit exceeded. For example, the following macro – supposed to compute the nth number of the Fibonacci sequence – fails:
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);db error: ERROR: stack depth limit exceeded
Function Chaining on the First Function
Macros support the dot operator for function chaining on the first function.
For example, the following macro uses the lower function in the conventional form:
CREATE OR REPLACE MACRO low(s) AS lower(s);
SELECT low('AA');low ----- aa
Rewriting lower(s) to use function chaining works as well:
CREATE OR REPLACE MACRO low(s) AS s.lower();
SELECT low('AA');low ----- aa
Viewing the List of Macros and Table Macros
You can use the following query to display the list of macros and table macros:
SELECT schema_name, function_name, function_type, parametersFROM sdb_functions()WHERE function_type IN ('macro', 'table_macro');