Comments of non-derived tables

Copy SQL

WITH named_objects AS (

SELECT 
c.oid,
n.nspname AS schema_name, 
c.relname AS table_name,
c.relname AS commented_object_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='f' THEN 'FOREIGN TABLE' 
WHEN c.relkind='p' THEN 'PARTITIONED TABLE' END AS commented_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 ('r','f','p')

UNION SELECT
o.oid,
n.nspname AS schema_name,
c.relname AS table_name,
conname AS commented_object_name,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='c' THEN 'TABLE CHECK'
WHEN o.contype='x' THEN 'EXCLUDE'
WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS commented_object_type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','f','p')

UNION SELECT 
tr.oid,
n.nspname AS schema_name,
c.relname AS table_name,
tr.tgname AS commented_object_name,
'TRIGGER' AS commented_object_type
FROM pg_trigger tr INNER JOIN pg_class c ON tr.tgrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND tr.tgisinternal='f'
AND c.relkind IN ('r','f','p')

UNION SELECT 
r.oid,
n.nspname AS schema_name,
c.relname AS table_name,
r.rulename AS commented_object_name,
CASE WHEN is_instead=TRUE THEN 'INSTEAD OF ' END ||
CASE WHEN ev_type='1' THEN 'SELECT'
WHEN ev_type='2' THEN 'UPDATE'
WHEN ev_type='3' THEN 'INSERT'
WHEN ev_type='4' THEN 'DELETE' END || ' RULE' AS commented_object_type
FROM pg_rewrite r INNER JOIN pg_class c ON r.ev_class=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND c.relkind IN ('r','f','p')

UNION SELECT 
c.oid,
n.nspname AS schema_name, 
c2.relname AS table_name,
c.relname AS commented_object_name,
'INDEX' AS commented_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
INNER JOIN pg_index i ON c.oid=i.indexrelid
INNER JOIN pg_class AS c2 ON i.indrelid=c2.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND c.relkind IN ('i','I')
AND c2.relkind IN ('r','f','p')

),
named_user_defined_objects AS (
SELECT oid, schema_name, table_name, commented_object_name, commented_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)
),
columns AS (SELECT 
c.oid,
n.nspname AS schema_name,
c.relname AS table_name,
at.attname AS commented_object_name,
'COLUMN' AS commented_object_type,
attnum AS ordinal_position
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','f','p')
AND at.attisdropped='f'
AND at.attnum>0
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=c.oid))

SELECT schema_name, table_name, commented_object_name, commented_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
WHERE objsubid=0
UNION SELECT schema_name, table_name, commented_object_name, commented_object_type, description AS comment
FROM columns AS c INNER JOIN pg_catalog.pg_description AS d ON c.oid=d.objoid AND c.ordinal_position=d.objsubid
ORDER BY schema_name, table_name, commented_object_type, commented_object_name;