== 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 This is very much a work in progress. If it doesn't do something you need, please https://github.com/decibel/cat_tools/issues[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` * `routine_type` - Types of routines stored in `pg_proc` * `routine_argument_mode` - Argument modes for function/procedure parameters * `routine_volatility` - Volatility levels for functions/procedures * `routine_parallel_safety` - Parallel safety levels for functions/procedures * `routine_argument` - Detailed information about a single function/procedure argument == General Introspection Functions * `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 * `pg_attribute__get(relation regclass, column_name name)` - Returns `pg_attribute` row for a column; throws error if column doesn't exist * `relation__column_names(relation regclass)` - Returns an array of quoted column names for a relation in ordinal position order * `relation__is_catalog(relation regclass)` - Returns true if the relation is in the `pg_catalog` schema * `relation__is_temp(relation regclass)` - Returns true if the relation is a temporary table (lives in a schema that starts with 'pg_temp') == Object Type Query Functions Functions for working with `object_type` values — the enum of descriptive names for every type of Postgres object (table, index, role, etc). * `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) * `object__reg_type_catalog(object_identifier_type regtype)` - Returns the system catalog that stores a particular object identifier type (inverse of `object__reg_type`) * `object__address_classid(object_type)` - Returns the classid used by the `pg_*_object*()` functions for an object_type * `objects__shared()` - Returns an array of object types that are shared catalog objects (as opposed to per-database) * `objects__shared_srf()` - Set returning version of `objects__shared` * `object__is_shared(object_type)` - Returns true if object_type is a shared object * `objects__address_unsupported()` - Returns array of object types not supported by `pg_get_object_address()` * `objects__address_unsupported_srf()` - Set returning version of `objects__address_unsupported` * `object__is_address_unsupported(object_type)` - Returns true if object type is not supported by `pg_get_object_address()` == Sequence Functions * `get_serial_sequence(table_name text, column_name text)` - Returns sequence associated with a column; unlike `pg_get_serial_sequence`, throws an exception if no sequence exists * `currval(table_name text, column_name text)` - Alias for `sequence__last` * `nextval(table_name text, column_name text)` - Alias for `sequence__next` * `setval(table_name text, column_name text, new_value bigint, has_been_used boolean DEFAULT true)` - Sets sequence value for a column; if `has_been_used` is true, sequence will return `new_value + 1` next * `sequence__last(table_name text, column_name text)` - Returns the last value assigned to a column's sequence * `sequence__next(table_name text, column_name text)` - Returns the next sequence value and advances the sequence * `sequence__set_last(table_name text, column_name text, last_value bigint)` - Sets last used value; sequence will return `last_value + 1` next * `sequence__set_next(table_name text, column_name text, next_value bigint)` - Sets the next value the sequence will return == Routine / Function / Procedure Functions * `routine__parse_arg_types(arguments)` - Accepts full function argument string and returns regtype[] of IN/INOUT arguments * `routine__parse_arg_types_text(arguments)` - Version of `routine__parse_arg_types` that returns text * `routine__parse_arg_names(arguments)` - Accepts full function argument string and returns text[] of IN/INOUT argument names * `routine__parse_arg_names_text(arguments)` - Version of `routine__parse_arg_names` that returns text * `routine__arg_types(regprocedure)` - Returns argument types for a function as regtype[] * `routine__arg_types_text(regprocedure)` - Version of `routine__arg_types` that returns text * `routine__arg_names(regprocedure)` - Returns argument names for a function as text[] * `routine__arg_names_text(regprocedure)` - Version of `routine__arg_names` that returns text * `regprocedure(routine_name, arguments)` - Returns regprocedure for routine_name and its full set of arguments == Trigger Functions * `trigger__args_as_text(text)` - Converts the arguments for a trigger function (as returned by `trigger__parse()`) to text (for backwards compatibility). * `trigger__get_oid(trigger_table, trigger_name)` - oid of a trigger. Throws error if trigger doesn't exits. * `trigger__get_oid__loose(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 == Mapping Functions * `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 * `routine__type(prokind)` - Mapping from `pg_proc.prokind` to `routine_type` * `routine__argument_mode(mode)` - Mapping from `pg_proc.proargmodes` element to `routine_argument_mode` * `routine__volatility(volatile)` - Mapping from `pg_proc.provolatile` to `routine_volatility` * `routine__parallel_safety(parallel)` - Mapping from `pg_proc.proparallel` to `routine_parallel_safety` == Deprecated Functions * `function__arg_types(arguments)` - DEPRECATED: Use `routine__parse_arg_types` instead * `function__arg_types_text(arguments)` - DEPRECATED: Use `routine__parse_arg_types_text` instead == 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 https://github.com/decibel/cattools/blob/master/LICENSE[MIT license]. Copyright (c) 2016-2026 Jim Nasby .