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: ascalar 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 youFunction "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. Perfect 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 aLIMIT 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.

Macros substitute text, not values

A macro has no declared parameter types and no return type. The body is substituted at the call site and then bound, so errors show up as if you had written the expression out by hand — passing text togross_margin reports a problem with the subtraction inside it, not with the macro.

That is mostly a feature: one macro works on integers, decimals, and doubles without you writing three. The cost is that a mistake points at the body rather than the call.

What macros cannot do

• Recursion. A macro cannot call itself; the name does not exist yet while the body is being defined.
• Default parameter values. DuckDB rejects theparam := value form in a macro definition. Every argument is required, so a second variant means a second macro.
• Procedural logic. No loops, no variables, no multiple statements. A macro is one expression or one query.
• Side effects. A macro cannot insert, update, or create anything.

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 andtable_macro for table macros — 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 — write the expression. Used in six queries by four people, it is the difference between one definition of margin and four.