Provider tables

A provider table is a real table to the planner, joinable and filterable and writable, whose rows come from a function in your process rather than from storage. You implement one by registering scalar UDFs and naming them once.

The API is SQL, so the host can be any DuckDB binding: C++, Python, Go, Node. Nothing below is specific to one of them.

The two statements

A provider lives in a virtual_catalog_provider catalog. One registration covers the whole catalog; table names are schema-qualified, and each schema is materialised when the provider lists a table in it:

-- 1. The catalog the provider is injected into.
ATTACH ':memory:' AS app (TYPE virtual_catalog_provider);

-- 2. Name the functions that back it ('' for an op it does not implement).
SELECT provider_register(
    'app',            -- target catalog (the ATTACH alias)
    'prov_list',      -- list   → 'schema.table' names
    'prov_schema',    -- schema → Arrow IPC schema message
    'prov_scan',      -- scan   → Arrow IPC stream
    'prov_insert',    -- insert
    'prov_update',    -- update
    'prov_delete',    -- delete
    'prov_alter'      -- alter
);

From here app.public.<table> is an ordinary table name. SELECT, JOIN, INSERT, UPDATE, DELETE, and ALTER TABLE all bind against it, and the planner does not know or care that a callback is answering.

The seven functions

Three are required. The rest are how a provider opts into being written to.

UDF	Called for	Answers with
`list`	table discovery	the `schema.table` names
`schema`	binding a name to columns	an Arrow IPC schema message
`scan`	`SELECT`, with columns and filters	an Arrow IPC stream
`insert`	`INSERT`	rows affected
`update`	`UPDATE`	rows affected
`delete`	`DELETE`	rows affected
`alter`	`ADD` / `DROP` / `RENAME COLUMN`	success

scan receives the projected columns and the pushed predicates, so a provider that reads Parquet or calls an API can narrow at the source instead of returning everything and letting DuckDB filter afterwards.

What crosses the boundary

Arrow, in both directions, and only once. schema answers with a serialized Arrow schema message carrying column names, types, and primary keys in one payload. scan answers with a whole IPC stream. The write ops receive one.

Nothing is rendered into SQL text on the way past and nothing lands in a temp table, so the rows a provider returns are decoded exactly once, by the extension that asked for them.

Primary keys ride in the schema's Arrow metadata under vcat.primary_keys, comma-separated. UPDATE and DELETE need them to address a row, and they read key values straight out of the buffers scan returned.

Capabilities follow the functions

There is no permission file for a provider. What it can do is what it implements, and n6k_table_permissions reports it:

Column	For a provider table
`kind`	`provider`
`writeable`	the provider implements `insert`
`editable`	the provider implements `alter`
`primary_key`	from the `vcat.primary_keys` Arrow metadata

That answer is a metadata lookup. It costs no call into the provider, so a client can ask what it may do before it offers a button.

Refreshing and teardown

DuckDB caches what a provider returned. When the underlying data moves, bump the version and the next lookup refills:

SELECT provider_invalidate_tables('app');
SELECT provider_unregister('app');

Re-registering the same catalog replaces the UDF set, which is how a host swaps its implementation without detaching.

Limits

RETURNING on INSERT / UPDATE / DELETE is not supported.
Primary-key columns must be a fixed-width signed integer or a string type. Other key types are refused with an error naming the column rather than misread. Non-key columns are unrestricted.
Only ADD COLUMN, DROP COLUMN, and RENAME COLUMN reach alter. Other ALTER kinds are rejected before they get there.
A provider UDF must not query the connection that called it. Open a cursor instead.

A table is an interface, not a file: implement seven functions and the planner cannot tell the difference.