Find the number of different types of schema objects in different schemas as well as the total number of schema objects. Show the number of objects that belong to an extension as well as the number of (user-defined) objects that do not belong to an extension.
Notes
Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. The query (due to the use of CUBE) also finds the number of schema objects in different schemas, the number of different types of schema objects, and the total number of schema objects. The query only returns data about schemas that have at least one schema object.
Type
Sofware measure (Numeric values (software measures) about the database)
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;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Find quick numeric overview of the database
Queries that return numeric values showing mostly the number of different types of database objects in the database
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.