Find tables by what they hold

search_catalog finds tables and views from a description of what you need, so an agent can work in a database whose names it has never seen. Search by words, narrow by name or schema, and page through a large catalogue.

Uses the sql helper from the Quickstart on the sezzlee_shop sample schema.

Search with words

query is a few words. Each is matched against schema, table, view and column names, and against the comments stored with them by COMMENT ON:

sql search_catalog --tool-arg query=revenue schema=sezzlee_shop \
  | jq -c '.results[] | {name, kind, matched}'

{"name":"order_totals","kind":"view","matched":[{"field":"description","term":"revenue","value":"Revenue per order, summed from its lines."}]}

Nothing is named "revenue"; the view's comment says it. Comments are how a catalogue with cryptic names becomes searchable, so it pays to write them.

A word also matches names that begin with it, and case and accents are ignored:

sql search_catalog --tool-arg query=SHIPP schema=sezzlee_shop \
  | jq -c '.results[] | {name, matched: [.matched[] | "\(.field): \(.value)"]}'

{"name":"orders","matched":["description: One row per purchase; status is pending, shipped or cancelled.","column: shipped_at","columnDescription: shipped_at"]}

Only the beginning of a word is matched, never the middle: ship finds shipped_at, but date does not find ordered_on, and tarih does not find FATURATARIH. The search reads names and comments, not rows. To find which table holds a given customer, search for customer, then query it.

Narrow by schema, name or kind

schema keeps one schema, namePattern is a LIKE pattern on the object's name, and includeViews=false leaves views out. With no query, the matches are listed by name:

sql search_catalog --tool-arg schema=sezzlee_shop namePattern="order%" \
  | jq -c '[.results[] | {name, kind}]'

[{"name":"order_lines","kind":"table"},{"name":"order_totals","kind":"view"},{"name":"orders","kind":"table"}]

Views include materialized views. The search sees tables, partitioned tables, foreign tables and views that the role can read, in schemas it may use; it never lists pg_catalog or information_schema.

Page through many results

maxResults caps one answer, 50 by default and 200 at most. When more remain, truncated is true and nextCursor continues with the same filters:

sql search_catalog --tool-arg schema=sezzlee_shop maxResults=2 \
  | jq -c '{names: [.results[].name], truncated}'

{"names":["customers","ledger"],"truncated":true}

Pass nextCursor back with the same query, schema, namePattern and includeViews; a cursor passed with other ones fails with stale_cursor.

After a schema change, or on a large catalogue

The server reads the catalogue once and reuses it for 15 minutes. A table created since then is not found until the copy expires, or until a search passes refresh=true, which reads the catalogue again and makes earlier cursors stale.

Each answer carries a catalog block. Its complete is false when the database has more than 5,000 tables and views or 50,000 columns, the most the search index holds. A search that finds nothing may then have missed an object past the cut. describe_table still reads any table whose name you already know, spelled as search_catalog returned it: PostgreSQL folds an unquoted name to lower case, so a table created as "Orders" is Orders here, and run_query has to quote it.