Macros

Macros are user-defined named blocks that you can use multiple times in your templates, reducing repetition. They are equivalent to functions in programming languages. You start the macro definition using the macro keyword and end it using endmacro.

Let's use an example to explain how macros work and how they can be useful to you. Imagine you have an SQL table containing several nullable columns: you want to query it and assign a default value in case of NULL, as well as an alias to make results more readable. Your query would look something like this:

SELECT 
    coalesce('first_name', 'N/A') as first_name,
    coalesce('last_name', 'N/A') as last_name,
    coalesce('business_name', 'N/A') as business_name,
    coalesce('business_vat_id', 'N/A') as business_vat_id
FROM learnsql.invoices
;

There's a lot of repetition in it: let's use a macro to remove it.

{%- macro if_null(column, default_value) -%}
    coalesce({{ column }}, '{{ default_value }}') as {{ column }}
{%- endmacro -%}

SELECT 
    {{ if_null('first_name', 'N/A') }},
    {{ if_null('last_name', 'N/A') }},
    {{ if_null('business_name', 'N/A') }},
    {{ if_null('business_vat_id', 'N/A') }}
FROM learnsql.invoices
;

Rendering the above template results in same query as above.

Another example: your table contains columns that provide VAT-including amounts as well as the applied VAT rate; you want to strip VAT off of the amounts, rounding them to up to 2 decimal digits. You'd normally write something like:

SELECT 
    discount_applied,
    total,
    vat_rate,
    round(discount_applied::numeric / (1 + vat_rate), 2)
    round(total::numeric / (1 + vat_rate), 2)
FROM learnsql.invoices
;

The VAT stripping and rounding logic take a moment to read, let's move them to a macro:

{%- macro strip_off_vat_and_round(amount, vat_rate) -%}
    round({{ amount }}::numeric / (1 + {{ vat_rate }}), 2)
{%- endmacro -%}

SELECT 
    discount_applied,
    total,
    vat_rate,
	{{ strip_off_vat_and_round('discount_applied', 'vat_rate') }},
	{{ strip_off_vat_and_round('total', 'vat_rate') }}
FROM learnsql.invoices
;

The macro name makes the intent clearer: the advantages of macros become more and more visible as the complexity of your query and/or the number of repetitions increases.