DuckDB Macros

A macro names a piece of SQL so you can use it by that name afterwards. There are two kinds, and they are used in different places: a scalar macro stands in for an expression and goes wherever a value goes, while a table macro stands in for a query and goes in the FROM clause.

create or replace macro gross_margin(revenue, cost) as
case
  when revenue = 0 then null
  else round((revenue - cost) / revenue, 3)
end;

select product_name,
       gross_margin(price, cost) as margin
from products
order by margin desc, product_name
limit 5;

Every place that CASE used to be copied, there is now a name that says what it means. When the definition of margin changes, it changes in one place.

Table macros

A table macro is the piece a view cannot do: a saved query that takes arguments. A view is fixed once created; a table macro is a view with parameters.

create or replace macro top_products(n) as table
select p.product_name,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join products p on p.product_id = oi.product_id
group by p.product_name
order by revenue desc, p.product_name
limit n;

select * from top_products(3);

Call it in the FROM clause like a table, including in joins and subqueries. Calling one as if it were a value gets you Function "top_products" is a table function but it was used as a scalar function, which is the clearest signal that you reached for the wrong kind.

Which one to use

Macro vs view vs CTE

All three save you from repeating SQL, and the deciding question is whether the saved thing needs to change per use.

• CTE: one statement, no object created. Fine until you need it in a second query.
• View: reusable, but frozen. Filter it at the call site with a WHERE.
• Table macro: reusable and parameterized, when the argument changes something a WHERE cannot reach, like a LIMIT or which column is grouped.

In practice a view plus a WHERE covers most cases and is more portable. Reach for a table macro when the parameter shapes the query rather than filters the rows.

Parameters, defaults, and overloads

Macro parameters can be left untyped when you want the body to work across compatible input types, or given explicit DuckDB types when you want binding to be stricter. The macro's result type is inferred from the expression. Because the macro body is expanded at the call site, type errors often point into that expanded expression.

create or replace macro discount(
  price decimal(10, 2),
  pct := 10
) as price * (1 - pct / 100.0);

select discount(50.00)        as default_discount,
       discount(50.00, 20)    as custom_discount;

DuckDB also supports macro overloading. Put the overloads in the same CREATE MACRO statement; a later CREATE OR REPLACE MACRO replaces the existing macro definition rather than appending another overload.

create or replace macro add_tax
  (amount) as amount * 1.06,
  (amount, rate) as amount * (1 + rate);

What macros cannot do

• Recursion. A macro cannot call itself while its own definition is being bound.
• Procedural logic. No loops, procedural variables, or statement blocks. A scalar macro expands to an expression; a table macro expands to a table-producing query.
• Side effects. A macro body is not a place for INSERT, UPDATE, or DDL statements. For what replaces a procedure that changes data, see stored procedures.

A scalar macro can contain a subquery over a table, which is more than the name suggests: create macro order_count() as (select count(*) from orders) works.

Inspecting them

select function_name,
       function_type,
       parameters,
       macro_definition
from duckdb_functions()
where function_name in ('gross_margin', 'top_products')
order by function_name

function_type is macro for scalar and table_macro for table macros, which is worth checking when a call fails and you are not sure which kind you created.

Remove one with DROP MACRO, which works on both kinds. DROP MACRO TABLE only accepts table macros.

Where they earn their keep

• A business definition repeated across queries (margin, active customer, billable session) where everyone must compute it the same way.
• An expression too fiddly to retype correctly, like a regex extraction or a nested coalesce.
• A report shape run with different arguments: top N, one region, one month.

Used once, a macro is indirection with no payoff, so write the expression. Used in six queries by four people, it is the difference between one definition of margin and four.