PostgreSQL: create_column_documentation_view.sql

-- PostgreSQL 9.3.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
-- ============================================================================
-- Returns the comments for all columns in a table.
--
-- Precondition:
--
--     Assumes you are currently using a database.
--
-- Usage:
--
--     SELECT * FROM column_documentation;
--
--     SELECT name, comment
--     FROM column_documentation
--     WHERE table_name = 'date_dimension';
--
-- ============================================================================
CREATE OR REPLACE VIEW column_documentation AS
    SELECT
        CAST(cols.table_name AS TEXT) AS table_name,
        CAST(cols.column_name AS TEXT) AS name,
        (
            SELECT
                pg_catalog.col_description(c.oid, cols.ordinal_position::int)
            FROM pg_catalog.pg_class c
            WHERE
                c.oid     = (SELECT cols.table_name::regclass::oid) AND
                c.relname = cols.table_name
        ) AS comment
    FROM information_schema.columns cols
    WHERE
        cols.table_catalog = current_database() AND
        cols.table_name IN (
            SELECT
                CAST(c.relname AS TEXT) AS table_name
            FROM pg_class AS c
            LEFT JOIN pg_description AS d ON (d.objoid = c.oid AND d.objsubid = 0)
            WHERE c.relkind IN('r', 'v') AND d.description > ''
            ORDER BY c.relname)
    ORDER BY cols.table_name, cols.ordinal_position;

COMMIT;