Calling table-valued functions with FILTER_PARAMS

Support Forum
1

If your database has an existing table-valued function (TVF) that accepts parameters (e.g. a date range), you can call it dynamically from a Cube model using FILTER_PARAMS. The function executes with the exact range the user selects in the dashboard — no static table scan required.

How it works

Write the cube’s sql as a subquery that:

  1. Uses FILTER_PARAMS twice — once to extract the from boundary, once for the to boundary — using a lambda that returns just one value each time.
  2. Passes those values as parameters to your function via CROSS JOIN LATERAL (PostgreSQL) or the equivalent for your database.
cubes:
  - name: my_tvf_cube
    sql: |
      SELECT t.*
      FROM (
        SELECT
          {FILTER_PARAMS.my_tvf_cube.event_time.filter(
            lambda x, y: f"{x}"
          )}::timestamp AS from_time,
          {FILTER_PARAMS.my_tvf_cube.event_time.filter(
            lambda x, y: f"{y}"
          )}::timestamp AS to_time
      ) params
      CROSS JOIN LATERAL public.my_function(params.from_time, params.to_time) t

    dimensions:
      - name: event_time
        sql: event_time
        type: time

      # ... other columns returned by your function

The lambda x, y: f"{x}" / lambda x, y: f"{y}" callbacks tell Cube to extract just the start or end boundary of the filter — rather than generating a full col >= x AND col <= y condition.

Supported databases

Database Lateral join syntax
PostgreSQL CROSS JOIN LATERAL my_func(from_time, to_time) t
Snowflake TABLE(my_func(from_time, to_time)) in the FROM clause
BigQuery CROSS JOIN UNNEST(my_tvf(from_time, to_time))
SQL Server CROSS APPLY dbo.my_func(from_time, to_time)

Can a TVF cube join to other models?

Yes. You can define joins on a TVF cube exactly as you would on any other cube. FILTER_PARAMS resolves correctly when the cube is queried alongside a joined model. A common pattern is enriching TVF results with a dimension table (e.g. businesses, users, products):

joins:
  - name: businesses
    sql: "{CUBE}.business_id = {businesses}.id"
    relationship: many_to_one

Two things to keep in mind:

  • Only matched rows are returned — dimension table rows with no corresponding TVF results are excluded. If a business has no transactions in the selected date range, it won’t appear in the results.
  • The join is defined on the TVF cube — the TVF cube owns the foreign key and declares the many_to_one relationship to the dimension table.

Do I need a TVF, or will a stored procedure work?

You need a table-valued function. Stored procedures won’t work here.

The reason is that CROSS JOIN LATERAL (and its equivalents) require something that returns a result set — i.e. something you can SELECT from. TVFs satisfy this because they return a table. Stored procedures do not — they use CALL and have no return value that can sit in a FROM clause.

This applies across all supported databases:

Database Stored procedure Table-valued function
PostgreSQL CREATE PROCEDURE + CALL — not queryable CREATE FUNCTION ... RETURNS TABLE ✓
SQL Server CREATE PROCEDURE + EXEC — not queryable CREATE FUNCTION ... RETURNS TABLE ✓
Snowflake CREATE PROCEDURE + CALL — not queryable CREATE FUNCTION ... RETURNS TABLE ✓

If you’re currently using a stored procedure, convert it to a table-valued function first. In PostgreSQL, that means replacing CREATE PROCEDURE with CREATE FUNCTION ... RETURNS TABLE (...).

Limitations

  • Time ranges only — the x/y callback only populates for inDateRange filters. Other filter types (equals, contains, etc.) don’t provide two boundary values.
  • No filter = likely error — if the user doesn’t apply a date range, both values are null, which will typically cause a database error or return no rows. Enforce a default date range in your component to prevent this.
  • No pre-aggregation support — pre-aggregations cannot accelerate queries built on TVFs.

Related docs