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 three statements

A provider lives in a virtual_catalog catalog, in a schema that already exists. Both come first:

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

-- 2. Name the functions that back it.
SELECT vcat_register_provider(
    'app',            -- target catalog (the ATTACH alias)
    'public',         -- target schema
    'inventory',      -- probe id, this registration's identifier
    'prov_list',      -- list   → 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 table names in this schema
`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 n6k.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 `n6k.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 vcat_invalidate_provider_tables('app', 'public');
SELECT vcat_unregister_provider('app', 'public');

Re-registering the same (catalog, schema) 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.