Table-Valued and Row-Producing Functions

Most Trilogy functions return one value for each input grain. Row-producing functions are different: they create a relation or expand one value into multiple rows.

Trilogy currently has one relational table-valued function, union(...), and two commonly used one-to-many functions, unnest(...) and date_spine(...).

union(...)

union(...) stacks the rows returned by two or more self-contained SELECT arms. It is Trilogy's equivalent of SQL UNION ALL: duplicates are preserved.

with <name> as union(
    (<select arm 1>),
    (<select arm 2>),
    ...
) -> (<output 1>, <output 2>, ...);

The arms are mapped to the output names by position. Every arm must return the same number of columns in the same order, with compatible types.

import store_sales as store;
import web_sales as web;

with sales_by_channel as union(
    (
        select
            'store' as channel,
            store.item.id as item_id,
            sum(store.sales_price) as sales,
    ),
    (
        select
            'web' as channel,
            web.item.id as item_id,
            sum(web.sales_price) as sales,
    )
) -> (channel, item_id, sales);

select
    sales_by_channel.channel,
    sales_by_channel.item_id,
    sales_by_channel.sales,
order by sales_by_channel.sales desc
limit 100;

Each arm may have its own WHERE, aggregation, or query-scoped joins. An arm must be written inline; a previously defined rowset name cannot be passed in place of an arm.

Re-aggregating stacked rows

The named outputs form the grain of the union result. Wrap a union output in an aggregate when you want to summarize the stacked rows:

select
    sales_by_channel.channel,
    sum(sales_by_channel.sales) as channel_sales,
by rollup (sales_by_channel.channel);

Use union(...) to stack rows. Use a query-scoped join to combine columns from relations by matching keys.

unnest(...)

unnest(array) expands an array into one row per element. Its output is a key, because it changes the grain of the result.

const priority <- unnest(['low', 'medium', 'high']);

select priority
order by priority asc;

It can also expand an array-valued concept from a datasource:

select
    article.id,
    unnest(article.tags) as tag,
;

The generated SQL varies by dialect. Trilogy renders the appropriate lateral, cross-join, UNNEST, FLATTEN, or arrayJoin form for the configured backend.

date_spine(...)

date_spine(start_date, end_date) produces a daily date key spanning the provided bounds. It is useful for filling gaps in sparse time series.

auto calendar_date <- date_spine(
    '2025-01-01'::date,
    '2025-01-31'::date
);

select calendar_date
order by calendar_date asc;

To enrich a spine with sparse facts, merge the fact date into the partial spine and aggregate at the spine grain:

auto calendar_date <- date_spine(
    date_add(current_date(), day, -30),
    current_date()
);

merge orders.order_date into ~calendar_date;

select
    calendar_date,
    count(orders.id) by calendar_date as order_count,
order by calendar_date asc;

The ~ modifier declares the incoming fact dates as a subset, so dates without facts remain available.

Choosing the right construct

Goal	Construct
Stack complete query results vertically	union(...)
Expand each item in an array	unnest(...)
Generate a continuous daily key	date_spine(...)
Match columns from separate models by key	subset join or union join
Reuse a named query result	rowset / with <name> as select ...