WITH routine_comments AS (SELECT
n.nspname AS routine_schema,
p.proname AS routine_name,
pg_get_function_identity_arguments(p.oid) AS parameters,
CASE WHEN p.prokind='f' THEN 'FUNCTION'
WHEN p.prokind='p' THEN 'PROCEDURE'
WHEN p.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type,
EXISTS (SELECT * FROM pg_catalog.pg_description AS d WHERE p.oid=d.objoid) AS is_comment_object,
pg_get_functiondef(p.oid)~*'(/[*]|--)' AS is_comment_in_body
FROM
pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.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 p.prokind<>'a'
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 Count(*) AS nr_of_routines,
Count(*) FILTER (WHERE is_comment_object='t' OR is_comment_in_body='t') AS has_any_comment,
Count(*) FILTER (WHERE is_comment_object='t') AS has_comment_object,
Count(*) FILTER (WHERE is_comment_in_body='t') AS has_comment_in_body,
Count(*) FILTER (WHERE is_comment_object='f' AND is_comment_in_body='f') AS has_no_comment
FROM routine_comments;