The number of user-defined routines based on their database usage

Copy SQL

WITH routines AS (SELECT 
 p.oid AS routine_oid,
  n.nspname AS routine_schema, 
  p.proname AS routine_name, 
  CASE WHEN t.typname='trigger' THEN 'TRIGGER ROUTINE' ELSE 'NON-TRIGGER ROUTINE' END AS routine_type,
  p.prosrc  AS routine_src
FROM 
pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
LEFT JOIN pg_catalog.pg_type t ON p.prorettype=t.oid
WHERE p.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') 
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=p.oid))
SELECT 
routine_schema, 
routine_type,
Count(*) AS number_of_routines,
Count(*) FILTER (WHERE routine_src~*'(insert|update|delete)[[:space:]]') AS number_of_routines_that_modify_data, 
Count(*) FILTER (WHERE routine_src!~*'(insert|update|delete)[[:space:]]' AND routine_src~*'select[[:space:]][^;]+from[[:space:]]') AS number_of_routines_that_only_read_data 
FROM routines
GROUP BY rollup (routine_schema,  routine_type)
ORDER BY routine_schema,  routine_type, Count(*) DESC;