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 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)

• objectreg_type_catalog(object_identifier_type regtype) - Returns the system catalog that stores a particular object identifier type (inverse of objectreg_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)

• objectsshared_srf() - Set returning version of objectsshared

• 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()

• objectsaddress_unsupported_srf() - Set returning version of objectsaddress_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

• routineparse_arg_types_text(arguments) - Version of routineparse_arg_types that returns text

• routine__parse_arg_names(arguments) - Accepts full function argument string and returns text[] of IN/INOUT argument names

• routineparse_arg_names_text(arguments) - Version of routineparse_arg_names that returns text

• routine__arg_types(regprocedure) - Returns argument types for a function as regtype[]

• routinearg_types_text(regprocedure) - Version of routinearg_types that returns text

• routine__arg_names(regprocedure) - Returns argument names for a function as text[]

• routinearg_names_text(regprocedure) - Version of routinearg_names that returns text

• regprocedure(routine_name, arguments) - Returns regprocedure for routine_name and its full set of arguments

Trigger Functions

• 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

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

• functionarg_types(arguments) - DEPRECATED: Use routineparse_arg_types instead

• functionarg_types_text(arguments) - DEPRECATED: Use routineparse_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 MIT license.

Copyright (c) 2016-2026 Jim Nasby <[email protected]>.