SELECT
n.nspname AS object_schema,
p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')' AS object_name,
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 object_type,
d.description AS comment
FROM
(pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid)
LEFT JOIN pg_catalog.pg_description d ON p.oid=d.objoid
WHERE
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
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 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) AND Upper(d.description)= d.description
AND d.description~'[[:alpha:]]'
AND d.description!~'^[[:space:]]*TODO[[:space:]]*$'
UNION SELECT n.nspname AS object_schema,
c.relname AS object_name,
CASE WHEN c.relkind='v' THEN 'VIEW' ELSE 'MATERIALIZED VIEW' END AS object_type,
d.description AS comment
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_authid u ON u.oid = c.relowner
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_description d ON c.oid=d.objoid
WHERE c.relkind IN ('v', 'm')
AND (n.nspname = 'public' OR u.rolname <> 'postgres')
AND Upper(d.description)= d.description
AND d.description~'[[:alpha:]]'
AND d.description!~'^[[:space:]]*TODO[[:space:]]*$'
ORDER BY object_type, object_schema, object_name, comment;