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 provides union(...) for stacking relations and one-to-many functions such as unnest(...) and date_spine(...). A datasource can also bind to a database table function with fixed or query-bound arguments.

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.

The following example includes three rows of sales data. Each example can be run independently; Copy includes its setup.

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

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. The included model has two articles with tags:

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. The included model has orders on January 1 and January 3:

auto calendar_date <- date_spine(
    '2025-01-01'::date,
    '2025-01-03'::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 ...