Add a custom COPY format

How a Go worker registers its own COPY formats, so users can COPY … FROM a source your worker knows how to parse and COPY … TO a destination it knows how to write — a proprietary format, a remote API, or a custom sink.

Prerequisites

A catalog (see Expose a catalog) — COPY formats are catalog-level, so they need an ATTACH.

The two interfaces

Unlike the function shapes, COPY handlers are small enough to implement directly — there is no Typed* adapter.

	`CopyFromFunction`	`CopyToFunction`
Always required	`Name()`, `Metadata()`, `ArgumentSpecs()` — same as any function
Names the format	`CopyFromFormat() string`	`CopyToFormat() string`
Does the work	`Read(ctx, params, path, expectedSchema, out)`	`Write(ctx, params, batch)` per batch, then `Close(ctx, params)` once
Registered with	`w.RegisterCopyFrom(f)`	`w.RegisterCopyTo(f)`

The COPY statement’s path is not an option — it is handed to Read directly, and reached from the bind context for a writer. Everything else you declare as ordinary vgi:"..."-tagged options.

Reading: `COPY … FROM`

// COPY options are named, so the tags carry no pos=. The source path is
// supplied by the COPY statement, never as an option.
type widgetOpts struct {
  NullString string `vgi:"doc=Token parsed as SQL NULL"`
  Delimiter  string `vgi:"default=',',doc=Field separator"`
  SkipRows   int64  `vgi:"default=0,doc=Leading lines to skip"`
}

type ReadWidgets struct{}

var _ vgi.CopyFromFunction = (*ReadWidgets)(nil)

func (*ReadWidgets) Name() string           { return "read_widgets" }
func (*ReadWidgets) CopyFromFormat() string { return "widgets" }

func (*ReadWidgets) Metadata() vgi.FunctionMetadata {
  return vgi.FunctionMetadata{Description: "Read the Acme widget format"}
}

func (*ReadWidgets) ArgumentSpecs() []vgi.ArgSpec {
  return vgi.DeriveArgSpecs(widgetOpts{})
}

func (*ReadWidgets) Read(ctx context.Context, params *vgi.ProcessParams,
  path string, expectedSchema *arrow.Schema, out *vgirpc.OutputCollector,
) error {
  batch, err := parseWidgets(path, expectedSchema, options)  // your parser
  if err != nil {
      return err
  }
  defer batch.Release()
  return vgi.Emit(out, batch)
}

ATTACH 'acme' (TYPE vgi, LOCATION './acmeworker');
CREATE TABLE targets (name VARCHAR, qty BIGINT);
COPY targets FROM 'widgets://inventory' (FORMAT 'acme.widgets', skip_rows 1);

Emit expectedSchema exactly

DuckDB inserts no cast between the scan and the INSERT. Your batches must match expectedSchema in both type and arity — the target table’s schema is what defines it, which is why Read is handed the schema rather than choosing one.

The FORMAT name is qualified by the attach alias

CopyFromFormat() returning "widgets" is not what users type. The extension namespaces formats per attach, so the SQL name is '<attach-alias>.<format>'. The bare name raises Catalog Error: Copy Function with name widgets does not exist!, helpfully suggesting the qualified one. Two workers may therefore both call their format widgets without colliding.

Writing: `COPY … TO`

A writer has two methods: Write runs once per input batch, fanned out across DuckDB’s sink threads; Close runs exactly once and is where the destination is finished.

shardKey below is any byte slice you choose — it names the append-log inside this execution’s storage scope, which is already isolated per query.

var shardKey = []byte("widget_shards")

type WriteWidgets struct{}

var _ vgi.CopyToFunction = (*WriteWidgets)(nil)

func (*WriteWidgets) Name() string         { return "write_widgets" }
func (*WriteWidgets) CopyToFormat() string { return "widgets_out" }

func (*WriteWidgets) Metadata() vgi.FunctionMetadata {
  return vgi.FunctionMetadata{Description: "Write the Acme widget format"}
}

func (*WriteWidgets) ArgumentSpecs() []vgi.ArgSpec {
  return vgi.DeriveArgSpecs(writeOpts{})
}

func (*WriteWidgets) Write(ctx context.Context, params *vgi.ProcessParams, batch arrow.RecordBatch) error {
  // Per batch, possibly parallel, possibly on another process.
  data, err := vgi.SerializeRecordBatch(batch)
  if err != nil {
      return err
  }
  _, err = params.Storage.StateAppend(shardKey, data)
  return err
}

func (*WriteWidgets) Close(ctx context.Context, params *vgi.ProcessParams) error {
  // Once. Read the shards back and finish the destination.
  entries, err := params.Storage.StateLogScan(shardKey, -1, 0)
  if err != nil {
      return err
  }
  // ... write and close the destination ...
  return nil
}

Give each direction its own format name

The extension registers COPY functions by name alone — its registry has no direction component, and on a hit it silently skips the entry. Since v0.22.0 the SDK catches that at registration rather than letting one handler vanish:

vgi: COPY format "widgets" is registered by handler "read_widgets" (from) and
handler "write_widgets" (to). The extension keys COPY functions by name alone and
would silently drop one of them. Give each direction its own format name
(e.g. "widgets" and "widgets_out"), or serve both from one handler

Name them apart, as the SDK’s own fixtures do: example_lines and example_lines_out. One name can serve both directions when a single handler does both — a format record carries one handler and one option schema — and the SDK merges that case to direction="both" automatically.

On v0.21.0 and earlier there was no warning: the format appeared only in the from direction and COPY … TO failed later with “Not implemented Error: COPY TO is not supported for FORMAT …”.

Write and Close may run in different processes

The destination must be fully written and closed inside Close — a writer that forgets leaves a silent partial file. And because the two can run in different worker processes (pool rotation, HTTP), any shard state Close needs must live in params.Storage, scoped by execution, or go to a destination that tolerates concurrent writers. Buffering on the receiver breaks silently under rotation, exactly as it does for a buffering function.

Discovery

vgi_copy_formats() lists everything the attached catalogs advertise — both directions, each tagged with its direction — with the option schema, types, defaults and doc descriptions taken from your struct tags:

SELECT catalog_name, format_name, direction, option_name, option_type
FROM vgi_copy_formats();

This is also the fastest way to confirm a writer registered: if the to row is missing, the format name almost certainly collides with a reader’s.

Next steps

Where formats are advertised from → Expose a catalog.
The storage Close reads its shards from → State storage.
Exact contracts → COPY formats.