Skip to content

README.asc

Latest commit

 

History

History
95 lines (73 loc) · 5.14 KB

README.asc

File metadata and controls

95 lines (73 loc) · 5.14 KB
Raw

Cat Tools

These are tools that make it easier to interface with the Postgres catalog tables/views/functions. They are meant for use by code, not by people.

To make use of them, you need to grant cat_tools__usage to any roles that need access.

Warning

Any function or view in this extension that exposes raw PostgreSQL catalog information does not provide a stable API. The PostgreSQL system catalogs change between major versions — columns are added, removed, and change type. If your code depends on the specific columns returned by objects such as cat_tools.pg_class_v, cat_tools.column, or cat_tools.pg_attribute_v, it may break when you upgrade PostgreSQL.

Current Status

PGXN version Build Status

This is very much a work in progress. If it doesn’t do something you need, please open an issue!

Supported Versions

Works on Postgres 9.3 and above.

Updating the extension

Usually just run ALTER EXTENSION cat_tools UPDATE. Two special cases apply to a database that reached 0.2.2 via the 0.2.0/0.2.1 update scripts, whose catalog views are broken (they still reference pg_class/pg_attribute columns — relhasoids, relhaspkey — that a fresh 0.2.2 install correctly omits). A fresh 0.2.2 install has correct views and is unaffected by either case.

Binary pg_upgrade to PostgreSQL 12+ from such a database

Update the extension to 0.2.3 (or newer) on the old cluster first, then run binary pg_upgrade. The broken views reference catalog columns removed in PG12+, so pg_upgrade fails otherwise; the 0.2.2→0.2.3 update repairs them first. See the 0.2.3 entry in HISTORY.asc for details.

Updating such a database to 0.2.3 rebuilds some public views

Only when the views are broken, the 0.2.2→0.2.3 update DROP+CREATE`s (without `CASCADE) these public objects: cat_tools.pg_class_v, cat_tools.column, and the cat_tools.pg_class(regclass) function. If you created your own objects depending on any of these, drop them before updating and recreate them afterward; the update aborts otherwise. A fresh 0.2.2 install’s views are left untouched, so its dependents are unaffected. See the 0.2.3 entry in HISTORY.asc for details.

Data Types

  • object_type - Descriptive names for every type of Postgres object (table, operator, rule, etc)

  • constraint_type - Types of constraints (domain constraint or table_constraint)

  • relation_type - Types of objects stored in pg_class

  • relation_relkind - Valid values for pg_class.relkind

Functions

  • currval(table, column) - Returns current value for a sequence owned by a column

  • enum_range(regtype) - Returns valid values for an ENUM as an array

  • enum_range_srf(regtype) - Returns valid values for an ENUM as a recordset

  • name__check(text) - Throws an error if input would be truncated when cast to name

  • pg_class(relation regclass) - Returns pg_class_v row for a relation

  • pg_extension__get(extension_name name) - Returns pg_extension_v row for an extension

  • extension__schemas(extension_names text/name[]) - Returns the schemas for the requested functions

  • extension__schemas_unique(extension_names text/name[]) - Returns a unique array of schemas

  • function__arg_types(arguments) - Accepts full function argument string and returns regtype[] of IN/INOUT arguments

  • functionarg_types_text(arguments) - Version of functionarg_types that returns text

  • object__catalog(object_type) - Returns catalog table that is used to store object_type objects

  • object__reg_type(object_catalog) - Returns the "reg" pseudotype (ie: regclass) associated with a system catalog (ie: pg_class)

  • regprocedure(function_name, arguments) - Returns regprocedure for function_name and it’s full set of arguments

  • relation__kind(relkind) - Mapping from pg_class.relkind to a relation_type

  • relation__relkind(relation_type) - Mapping from relation_type to a pg_class.relkind value

  • triggerargs_as_text(text) - Converts the arguments for a trigger function (as returned by triggerparse()) to text (for backwards compatibility).

  • trigger__get_oid(trigger_table, trigger_name) - oid of a trigger. Throws error if trigger doesn’t exits.

  • triggerget_oidloose(trigger_table, trigger_name) - oid of a trigger. Does not throw error if trigger doesn’t exits.

  • trigger__parse(trigger oid) - Returns information about a trigger

  • trigger__parse(table_name regclass, trigger_name text) - Returns information about a trigger

Views

Warning
 These views may eventually move into a separate extension!

  • pg_class_v - Joins pg_class to pg_namespace

  • pg_extension_v - Joins pg_extension to pg_namespace; cast extconfig to regclass[]

  • column - Returns data about columns

  • pg_all_foreign_keys - Data about foreign keys

Copyright and License

Cat Tools is released under a MIT license.

Copyright (c) 2016-2026 Jim Nasby <Jim.Nasby@gmail.com>.