1

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

  • 1
    You'll have to include pg_depend, which contains dependencies between the extension and its objects. – Laurenz Albe Dec 11 '19 at 17:27
  • @LaurenzAlbe This is it! Thank you very much – oᴉɹǝɥɔ Dec 11 '19 at 21:21
1

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
| improve this answer | |
  • You should probably restrict the dependency type to e for "member of the extension". – Laurenz Albe Dec 12 '19 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.