DuckDB SQL Macros: The Complete Guide to Reusable SQL Functions

TL;DR: DuckDB’s CREATE MACRO lets you package recurring SQL logic into parameterized, reusable functions. One-line calls replace entire CTE blocks. This guide covers syntax, default parameters, structured returns, view comparisons, and real strategies.

1. Why You Need Macros

Have you ever encountered these scenarios while writing SQL:

• Writing the same “working days calculation” logic every month, copy-pasting it 5 times
• A complex date expression scattered across 10 queries—change one, update ten
• Every team member has their own “utility CTE,” versions are inconsistent, bugs appear

The traditional solution is Views, but views have two fatal flaws:

1. No parameters—you can’t write SELECT * FROM get_weekly_report('2026-08-01')
2. Cannot compose expressions—views only return whole tables, not computed values

DuckDB’s Macros solve both problems.

2. Basic Usage: Define Once, Call Everywhere

2.1 Simplest Macro

-- Define: Calculate working days between two dates
CREATE MACRO workdays(start_date, end_date) AS
 (end_date::DATE - start_date::DATE + 1)
 - (date_part('dow', start_date::DATE) + date_part('dow', end_date::DATE));

-- Call
SELECT workdays('2026-08-10', '2026-08-17') AS result;
-- Returns: 5 (weekends excluded)

Note: A macro body is an expression, not a complete SELECT statement. This allows macros to be embedded anywhere an expression is expected.

2.2 Multi-Expression Macro

-- Define: Check if a date is a holiday (simplified)
CREATE MACRO is_holiday(date_val) AS
 CASE date_val::DATE
 WHEN '2026-01-01' THEN true
 WHEN '2026-05-01' THEN true
 WHEN '2026-10-01' THEN true
 ELSE false
 END;

-- Use in query
SELECT
 order_date,
 amount,
 is_holiday(order_date) AS is_holiday_flag
FROM orders
WHERE is_holiday(order_date);

3. Advanced: Default Parameters & Structured Returns

3.1 Default Parameter Values

-- Safe division: return default when divisor is 0
CREATE MACRO safe_divide(a, b, default_val := 0) AS
 CASE WHEN b = 0 THEN default_val ELSE a / b END;

SELECT
 safe_divide(10, 3) AS normal, -- 3.333...
 safe_divide(10, 0) AS zero; -- 0

Default parameters make macros flexible—call them without providing all arguments.

3.2 Structured Returns (struct)

-- Split a date into year/month/day
CREATE MACRO parse_date(d) AS
 struct_pack(
 year := extract('year' FROM d),
 month := extract('month' FROM d),
 day := extract('day' FROM d)
 );

SELECT parse_date('2026-08-13'::DATE);
-- Returns: {'year': 2026, 'month': 8, 'day': 13}

-- Destructured usage
SELECT
 (parse_date(order_date)).year AS order_year,
 (parse_date(order_date)).month AS order_month,
 SUM(amount) AS total
FROM orders
GROUP BY order_year, order_month;

3.3 List Macros

-- Generate N consecutive dates
CREATE MACRO date_range(start, count) AS
 generate_series(start, start + INTERVAL (count - 1) DAY, INTERVAL 1 DAY);

SELECT date_range('2026-08-01', 7);
-- Returns: [2026-08-01, 2026-08-02, ..., 2026-08-07]

4. Macro vs View vs CTE: Which to Choose?

Selection rules:

• Need parameterized expressions → use macros
• Need to return a whole table referenced in multiple places → use views
• Temporary logic within a single query → use CTE
• Need cross-session persistence for complex queries → use materialized views

5. Practical: Three Macros You Can Use Today

5.1 Money Formatting Macro

CREATE MACRO fmt_money(val, currency := '¥') AS
 currency || ROUND(val, 2);

SELECT fmt_money(1234.5), fmt_money(567.8, '$');
-- Returns: ¥1234.50 | $567.80

5.2 MoM Growth Calculation Macro

CREATE MACRO calc_mom(current_val, prev_val) AS
 ROUND(100.0 * (current_val - prev_val) / NULLIF(prev_val, 0), 2);

-- Usage in analysis query
SELECT
 month,
 revenue,
 LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
 calc_mom(revenue, LAG(revenue) OVER (ORDER BY month)) AS mom_pct
FROM monthly_sales;

5.3 Data Quality Check Macro

CREATE MACRO check_null_ratio(table_ref, col_name) AS
 ROUND(
 100.0 * SUM(CASE WHEN {col_name} IS NULL THEN 1 ELSE 0 END)
 / COUNT(*),
 2
 );

-- Check null ratio of user_id column in orders table
SELECT check_null_ratio(orders, user_id) AS null_ratio_pct;

6. Comparison with Traditional Tools

Core advantage: Macros give you functional programming capabilities without leaving SQL.

8. Summary

DuckDB’s macro feature is severely underrated. It fills the gap between “views can’t take parameters” and “CTEs can’t cross-query reuse,” letting you write reusable, configurable computation logic in pure SQL.

Key takeaways:

1. Macros are expression-level reuse, not table-level
2. Support default parameters for flexible calling
3. Can return scalars, structs, lists, and other types
4. Better than views for parameterized scenarios, better than CTEs for cross-query reuse

Next time you find yourself copy-pasting the same SQL more than 3 times, pause and ask: can this become a macro?

📖 More DuckDB macro case studies → duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。