The number of user-defined non-trigger routines by schema, by security type, and by being deterministic

Copy SQL

WITH routines AS (SELECT 
routine_name,
routine_schema,
security_type,
is_deterministic
FROM 
information_schema.routines
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND (data_type<>'trigger' OR data_type IS NULL)
AND routine_name NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match')
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d inner join pg_catalog.pg_proc pc ON d.objid=pc.oid
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
pc.proname || '_' || pc.oid = routines.specific_name))
SELECT 
routine_schema,
security_type,
is_deterministic,
Count(*) AS nr_of_routines
FROM routines
GROUP BY ROLLUP (routine_schema, security_type, is_deterministic)
ORDER BY routine_schema, security_type, is_deterministic, Count(*) DESC;