The number of schema objects by schema, by type, and in total

Copy SQL

WITH schema_objects AS (SELECT 
c.oid,
nspname AS schema_name, 
CASE WHEN relkind='r' THEN 'TABLE'
WHEN relkind='i' THEN 'INDEX'
WHEN relkind='S' THEN 'SEQUENCE GENERATOR'
WHEN relkind='t' THEN 'TOAST TABLE'
WHEN relkind='v' THEN 'VIEW'
WHEN relkind='m' THEN 'MATERIALIZED VIEW'
WHEN relkind='c' THEN 'COMPOSITE TYPE'
WHEN relkind='f' THEN 'FOREIGN TABLE'
WHEN relkind='p' THEN 'PARTITIONED TABLE'
WHEN relkind='i' THEN 'PARTITIONED INDEX'
END AS type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (nspname='public' OR rolname<>'postgres')
UNION ALL SELECT 
pt.oid,
nspname AS schema_name, 
CASE WHEN typtype='b' THEN 'BASE TYPE'
WHEN typtype='c' THEN 'COMPOSITE TYPE'
WHEN typtype='d' THEN 'DOMAIN'
WHEN typtype='e' THEN 'ENUMERATION TYPE'
WHEN typtype='r' THEN 'RANGE TYPE' END AS type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (nspname='public' OR rolname<>'postgres')
UNION ALL SELECT 
pc.oid,
nspname AS schema_name, 
CASE WHEN prokind='f' THEN 'FUNCTION'
WHEN prokind='p' THEN 'PROCEDURE'
WHEN prokind='a' THEN 'AGGREGATE FUNCTION'
WHEN prokind='w' THEN 'WINDOW FUNCTION' END AS type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace
WHERE (nspname='public' OR rolname<>'postgres'))
SELECT schema_name, type,  
Count(*) FILTER (WHERE 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=schema_objects.oid)) AS number_of_objects_in_an_extension, 
Count(*) FILTER (WHERE 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=schema_objects.oid)) AS number_of_user_defined_objects,
Count(*) AS total_number_of_objects
FROM schema_objects
GROUP BY CUBE (schema_name, type)
ORDER BY schema_name, type, Count(*) DESC;