snippetsqlMinor
How to select functions that belong in a given extension in PostgreSQL?
Viewed 0 times
postgresqlfunctionsbelongextensionthathowselectgiven
Problem
I am trying to compose a
To pull function list I use (a simplified version)
To select extensions I can run
Now I am looking for a link between extensions and functions they create when "
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_schemaSolution
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, 3Code Snippets
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, 3Context
StackExchange Database Administrators Q#255412, answer score: 8
Revisions (0)
No revisions yet.