Comments of schemas, sequences, types, domains, domain constraints, and event triggers

Copy SQL

WITH named_objects AS (

SELECT 
 n.oid,
 ''::text AS container_name, 
'DATABASE' AS container_type,
n.nspname AS object_name,
'SCHEMA' AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname<>'public' AND a.rolname<>'postgres')

UNION SELECT 
c.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
c.relname AS object_name,
CASE WHEN c.relkind='S' THEN 'SEQUENCE GENERATOR'
WHEN c.relkind='c' THEN 'COMPOSITE TYPE'
END AS object_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 (n.nspname='public' OR a.rolname<>'postgres')
AND relkind IN ('c','S')

UNION ALL SELECT 
pt.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
pt.typname AS object_name,
CASE WHEN pt.typtype='d' THEN 'DOMAIN'
WHEN pt.typtype='e' THEN 'ENUMERATION TYPE'
WHEN pt.typtype='r' THEN 'RANGE TYPE' END AS object_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 (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')

UNION SELECT
o.oid,
n.nspname || '.' || t.typname AS container_name,
'DOMAIN' AS container_type,
conname AS object_name,
'DOMAIN CHECK CONSTRAINT' AS object_type
FROM pg_constraint o INNER JOIN pg_type t ON o.contypid=t.oid
INNER JOIN pg_namespace AS n ON n.oid=t.typnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND o.contype='c'

UNION SELECT 
oid,
'' AS container_name,
'DATABASE' AS container_type,
evtname AS object_name,
'EVENT TRIGGER' AS object_type
FROM pg_event_trigger 

),
named_user_defined_objects AS (
SELECT oid, container_name, container_type, object_name, object_type
FROM named_objects nob
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=nob.oid)
)
SELECT container_name, container_type, object_name, object_type, description AS comment
FROM named_user_defined_objects AS n INNER JOIN pg_catalog.pg_description AS d ON n.oid=d.objoid
ORDER BY container_type, container_name, object_type, object_name;