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.

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

Supported Versions

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

Updating such a database to 0.2.3 rebuilds some public views

• 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

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

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

• 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__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__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

• 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

• function__arg_types(arguments) - DEPRECATED: Use routine__parse_arg_types instead

• function__arg_types_text(arguments) - DEPRECATED: Use routine__parse_arg_types_text instead

• 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

Cat Tools is released under a MIT license.

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