Goal Find all routines that belong to an extension.
Notes There could be multiple routines with the same name but with different parameters in the same schema (overloading). Thus, for the unique identification of the routine it is necessary to present also its parameters in addition to the schema name and routine name.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Data Source system catalog only
SQL Query 
SELECT 
  n.nspname AS routine_schema, 
  p.proname AS routine_name, 
  pg_get_function_identity_arguments(p.oid) AS routine_parameters,
  e.extname AS extension_name,
  e.extversion AS extension_version,
CASE WHEN p.prokind='f' THEN 'FUNCTION'
  WHEN p.prokind='p' THEN 'PROCEDURE'
  WHEN p.prokind='a' THEN 'AGGREGATE FUNCTION'
  WHEN p.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type
FROM 
  pg_catalog.pg_proc p, 
  pg_catalog.pg_namespace n,
  pg_catalog.pg_depend d,
  pg_catalog.pg_extension e
WHERE 
  p.pronamespace = n.oid
  AND p.oid=d.objid
  AND e.oid=d.refobjid
ORDER BY extension_name, routine_schema, routine_name, routine_parameters;

Categories

This query is classified under the following categories:

Extensions

Queries of this category provide information about extensions in the database.

NameDescription
ExtensionsQueries of this category provide information about extensions in the database.