How to select functions that belong in a given extension in PostgreSQL?

Question

I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function.

To pull function list I use (a simplified version)

SELECT pg_proc.proname, pg_namespace.nspname AS proschema
FROM pg_proc
    INNER JOIN pg_namespace ON (pg_namespace.oid = pg_proc.pronamespace)

To select extensions I can run

SELECT pg_extension.extname, pg_namespace.nspname as extschema
FROM pg_catalog.pg_extension
    INNER JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pg_extension.extnamespace)

Now I am looking for a link between extensions and functions they create when "CREATE EXTENSION". The SELECT should ideally produce a list of extension_name, extension_schema, foo_name, foo_schema

oᴉɹǝɥɔ · Answer 1 · 2019-12-13T03:44:49.727

All credit for this answer goes to LaurenzAlbe who hinted the solution in the comment. The query that lists functions created by an extension is

SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
    INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
    INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
    INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
    INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
ORDER BY 1, 3

How to select functions that belong in a given extension in PostgreSQL?

1 Answers1