The list of all the queries

Comments of derived tables

Query goal: Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information.
Notes about the query: The query takes into account both "ordinary" views and materialized views (snapshots). The query does not find comments of objects that are added to a database as a part of an extension.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

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='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW' 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 ('v','m')

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 ('v','m')

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 ('v','m')

),
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 ('v','m')
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;

Collections where the query belongs to

Collection nameCollection description
Find problems by overviewQueries 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 .

Categories where the query belongs to

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
CommentsQueries of this category provide information about comments to the database objects that have been registered in the system catalog.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.

Reference materials for further reading

Reference
https://blog.codinghorror.com/when-good-comments-go-bad/
https://blog.codinghorror.com/code-tells-you-how-comments-tell-you-why/
https://blogs.msdn.microsoft.com/ericlippert/2004/05/04/aargh-part-five-comment-rot/
https://www.postgresql.org/docs/current/sql-comment.html

The list of all the queries