List PostgreSQL Extension Objects with pg_describe_object

calendar Nov 10, 2026 / · 9 min read · extensions catalog queries dependencies psql fuzzystrmatch pg_depend pg_extension  ·
Share on: linkedin copy

List PostgreSQL Extension Objects with pg_describe_object

You ran CREATE EXTENSION fuzzystrmatch, and before you move, dump or drop it you need to know what it put in the database. In psql, \dx+ fuzzystrmatch lists every object that belongs to the extension. That command runs one query against pg_depend. The query returns one row per member object, and pg_describe_object turns each one into a readable label.

Purpose and Overview

Extensions group database objects so that you can manage them as one unit. When it runs, CREATE EXTENSION records the identity of every object it creates, so that DROP EXTENSION can remove them later. That record lives in pg_depend, the catalog that stores dependency relationships between database objects. Each member object gets a row that points at the extension's row in pg_extension, with deptype set to 'e'.

PostgreSQL will not let you drop one member on its own; only dropping the whole extension removes it. pg_dump also skips the members and writes a single CREATE EXTENSION command in their place. If someone edited a member's definition by hand, that edit is not in the dump.

The query returns one column, Object description, with a text label for each member, sorted alphabetically. It returns no OIDs and no owners. Members are not limited to functions and views. An extension script can also create data types, operators and index support methods, and each of those gets its own row.

Sample Code

The query psql runs for \dx+, aimed at one extension by name:

 1SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS "Object description"
 2FROM pg_catalog.pg_depend
 3WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass
 4  AND refobjid = (SELECT oid FROM pg_catalog.pg_extension WHERE extname = 'fuzzystrmatch')
 5  AND deptype = 'e'
 6ORDER BY 1;
 7
 8-- Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
 9-- Portions Copyright (c) 1994, The Regents of the University of California
10-- PostgreSQL Licence: https://raw.githubusercontent.com/postgres/postgres/master/COPYRIGHT

The same filter across every installed extension, joined to pg_extension for the name and version:

1SELECT e.extname,
2       e.extversion,
3       pg_catalog.pg_describe_object(d.classid, d.objid, 0) AS "Object description"
4FROM pg_catalog.pg_depend d
5JOIN pg_catalog.pg_extension e ON e.oid = d.refobjid
6WHERE d.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass
7  AND d.deptype = 'e'
8ORDER BY 1, 3;

Notes: Both blocks need PostgreSQL 9.1 or later, the release where pg_extension and pg_describe_object first appear. In the first block, psql's column-alias placeholder becomes "Object description", and the extension OID it passes in becomes a lookup on extname. To inspect a different extension, change 'fuzzystrmatch' to its name. If that extension is not installed, the subquery returns NULL and the query returns no rows. For fuzzystrmatch, the result names the module's functions, among them soundex, difference, levenshtein, metaphone, dmetaphone and dmetaphone_alt.

Code Breakdown

The query is a single filtered read of pg_depend.

The pg_depend columns

Each row in pg_depend pairs a dependent object with a referenced object. classid is the OID of the system catalog that holds the dependent object, and objid is the OID of the object itself. refclassid and refobjid do the same for the referenced side. In a member row, the dependent is the function, type or other member object, and the referenced object is the extension.

objsubid holds a column number when the dependent is a table column, and zero for every other object type. The query passes a literal 0 as the sub-object ID, so each label describes the whole object.

refclassid and the regclass cast

A bare OID in refobjid does not say which catalog it belongs to. refclassid supplies that. The cast 'pg_catalog.pg_extension'::pg_catalog.regclass converts the catalog's name to its OID, so the filter compares OID to OID. With both columns pinned, the referenced object must be an extension, and it must be the one you named.

Every name in the query carries the pg_catalog. prefix. This ties each lookup to the system catalog instead of whatever the search path finds first.

deptype = 'e' versus 'x'

'e' is DEPENDENCY_EXTENSION. The dependent object is a member of the extension and can be dropped only through DROP EXTENSION. It works the same way as an INTERNAL dependency, but PostgreSQL keeps it as a separate type for clarity and to make pg_dump simpler.

'x' is DEPENDENCY_AUTO_EXTENSION, and it does not mean membership. The object cannot work without the extension and is dropped automatically along with it. It is not a member, so pg_dump still dumps it. The query filters on 'e' alone, so it lists the objects whose definitions pg_dump leaves out. Configuration tables and sequences that the extension script marks with pg_extension_config_dump are still in this list, but pg_dump writes out their contents. To see the 'x' objects as well, change the filter to deptype IN ('e', 'x') and add deptype to the select list.

pg_describe_object

pg_describe_object(classid, objid, objsubid) returns a text description of the object identified by a catalog OID, an object OID and a sub-object ID. The text is meant for people to read, and depending on server configuration it might be translated. Do not parse it in scripts. For undefined objects the function returns NULL. When a script needs stable output, pg_identify_object returns machine-readable information that is never translated.

Key Extension Dependency Concepts

Member objects

An extension's name is unqualified and must be unique across the database. The extension itself does not live in a schema, but its member objects can. Objects that span the whole database cluster, such as databases, roles and tablespaces, cannot be extension members, so they never appear in this list.

DROP EXTENSION and RESTRICT

DROP EXTENSION drops the member objects as well. It also drops any routine marked as dependent with ALTER ROUTINE ... DEPENDS ON EXTENSION. Only the extension's owner can run it. With RESTRICT, the drop is refused if any object depends on the extension other than the extension itself, its members and those explicitly dependent routines.

Schema placement with CREATE EXTENSION ... SCHEMA

If an extension allows its contents to be relocated, the SCHEMA clause chooses where its objects go, as in CREATE EXTENSION hstore SCHEMA addons;. The named schema must already exist. With no SCHEMA clause and no schema in the control file, PostgreSQL uses the current default creation schema. If the control file names a schema, a SCHEMA clause cannot override it.

The relocatable control-file setting defaults to false. For a relocatable extension, ALTER EXTENSION ... SET SCHEMA moves every member into the new schema in one command.

Trusted extensions and ownership

An extension marked trusted in its control file can be installed by any user who has CREATE privilege on the current database. fuzzystrmatch is a trusted module. In a trusted install, the member objects belong to the bootstrap superuser, not the user who ran CREATE EXTENSION, unless the extension's script assigns them to that user.

Practical Applications

Each scenario below starts from the first block for one extension, or the second block when you need every extension in the database at once.

Pre-drop impact check

Run the first block before you drop anything. The output lists the extension's member objects, all of which DROP EXTENSION removes. The drop also removes objects with deptype = 'x' and routines marked DEPENDS ON EXTENSION, and the 'e' filter does not show them. To list the 'x' objects too, widen the filter to deptype IN ('e', 'x'), as described in the deptype breakdown above. Then run the drop with RESTRICT. If objects outside the extension depend on it, the command fails, and you can deal with those objects before you try again.

Auditing a trusted install

On a server where non-superusers install trusted extensions such as fuzzystrmatch, the member list shows what entered the database. Those members belong to the bootstrap superuser. Check that the target schema is one where untrusted users do not have, and will not get, CREATE privilege.

Dump and migration review

A dump includes a CREATE EXTENSION command for each extension instead of the member definitions. On restore, those objects come from the extension's own install script. Any hand edits to a member are lost. Compare the member list against your change history before a migration. Two kinds of object are exceptions. Objects with deptype = 'x' are dumped normally. Configuration tables marked with pg_extension_config_dump keep their contents in the dump, but not their definitions.

Encoding check before using fuzzystrmatch on UTF-8 data

The member list confirms which fuzzystrmatch functions are present. Four of them, soundex, metaphone, dmetaphone and dmetaphone_alt, do not work well with multibyte encodings such as UTF-8. Check the database encoding before building matching logic on those functions.

Version Compatibility

Extensions and the pg_describe_object function both arrived in PostgreSQL 9.1. The 9.1 function reference lists pg_describe_object with the same three arguments the query passes. Both blocks therefore run on 9.1 and every later release.

PostgreSQL 13 changed two things that affect extension work. It removed the FROM option of CREATE EXTENSION, so installations still using unpackaged extensions had to move to a packaged version before upgrading to 13. It also added trusted extensions, which non-superusers with CREATE on the database can install.

Best Practices

  • List members before every drop — run the query first, so the effect of DROP EXTENSION is known before you run it.
  • Write RESTRICT on DROP EXTENSION — the drop then fails while objects outside the extension still depend on it.
  • Give relocatable extensions their own schema — create the schema first, then install with CREATE EXTENSION ... SCHEMA.
  • Keep CREATE on that schema away from untrusted users — never grant it to untrusted users now or later.
  • Leave member objects unedited — pg_dump does not dump changes to member definitions.
  • Check pg_available_extensions before installing — it and pg_available_extension_versions show what this server can load.

References

Posts in this series