Table functions as a first-class abstraction

ClickHouse has built-in table functions such as numbers(), url(), remote() and s3(): things that look like a table but are produced by a call with arguments. What it has never had is a way for you to define one in SQL.

What has landed since this was written

When this was first drafted, the request was “parametrised views”. They exist now, and work as the draft hoped:

CREATE VIEW by_date AS
SELECT * FROM events WHERE event_time >= {since:DateTime};

SELECT * FROM by_date(since = '2026-01-02 00:00:00');

I confirmed on 26.4 that the call form works. What has not landed is the second half of the proposal, below.

The argument: a view is the wrong container for some of these

A view belongs to a database. In ClickHouse a database is not an isolation boundary: it is closer to a schema, like public in PostgreSQL or dbo in SQL Server. A view in default can read tables in test. So what does “belongs to a database” buy you? Two things only:

1. You can omit the database name when calling it.
2. Grants on the database also apply to the view (but reading other databases still needs their own permissions).

For some uses, those are what you want. For others, they are an obstacle: a function that fetches data from an external source or multiplexes several tables has no natural home database. UDFs already learned this lesson: they are global, not prefixed. So the proposal is both: let the author choose.

• Create it as a parametrised view, and it lives in a database with the grants that implies.
• Create it as a table function, and it is global, independent of any database.

Use cases for the view form

Force the sort-key filter. A huge table where an unfiltered SELECT is a mistake: expose only a view that requires the leading key, and grant SELECT on the view alone.

CREATE VIEW events_by_day AS
SELECT * FROM events WHERE event_time >= {day:Date} AND event_time < {day:Date} + 1;

SELECT count() FROM events_by_day(day = '2026-01-02');

Multiplexing. Several tables with the same shape behind one name, chosen by a parameter, so callers do not write the UNION ALL:

CREATE VIEW all_events AS
SELECT * EXCEPT source FROM
(
    SELECT 'web'    AS source, * FROM web_events
    UNION ALL
    SELECT 'mobile' AS source, * FROM mobile_events
)
WHERE source = {source:String};

Use cases for the table-function form

A REST client for SQL. Wrap url() once, with the URL and format baked in, and give every database and user a clean call:

-- hypothetical syntax, not implemented
CREATE TABLE FUNCTION currency_rate AS
SELECT * FROM url('https://api.example.com/rates', CSV, 'currency String, rate Float32')
WHERE currency = {currency:String};

SELECT * FROM currency_rate('EUR');

The query reads like a function call and hides the endpoint, the format and the schema. This would make ClickHouse a pleasant client for the HTTP services next to it, with the “complexity hidden from the user” that table functions give for free in the built-in case.

I checked 26.4: CREATE TABLE FUNCTION is not valid syntax. The pieces exist (parametrised views for the parameter binding, url() for the source); the global, database-independent container does not.

Where it connects

This is the same scope argument as for UDFs: see What ClickHouse UDFs should be able to do. A table function that can be scoped (global, per database, or hidden until switched on) and that is subject to the same EXECUTE-style grants would round out the extension story: scalar functions, aggregate functions and table functions, all definable in SQL, all under access control.