SQL
Examples
#### Scalar Macros {#docs:sql:statements:create_macro::scalar-macros}  
Create a macro that adds two expressions (` a` and `b`):  
```sql
CREATE MACRO add(a, b) AS a + b;
```  
Create a macro for a case expression:  
```sql
CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END;
```  
Create a macro that does a subquery:  
```sql
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.  
```sql
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`:  
```sql
CREATE FUNCTION main.my_avg(x) AS sum(x) / count(x);
```  
Create a macro with default constant parameters:  
```sql
CREATE MACRO add_default(a, b := 5) AS a + b;
```  
Create a macro `arr_append` (with a functionality equivalent to `array_append`):  
```sql
CREATE MACRO arr_append(l, e) AS list_concat(l, list_value(e));
```  
#### Table Macros {#docs:sql:statements:create_macro::table-macros}  
Create a table macro without parameters:  
```sql
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):  
```sql
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):  
```sql
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:  
```sql
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:  
```sql
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]);
```  
To define macros on arbitrary tables, use the [`query_table` function](#docs:guides:sql_features:query_and_query_table_functions). For example, the following macro computes a column-wise checksum on a table:  
```sql
CREATE MACRO checksum(table_name) AS TABLE
SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
FROM query_table(table_name);

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