Re: Create view that retrieves both table and column comments

On 02/18/2016 12:35 PM, Killian Driscoll wrote:
> Using an amended sql from here
> http://www.developerfiles.com/adding-and-retrieving-comments-on-postgresql-tables/
> I can create a view with three columns including the comments from one
> table:

> 1. I want to be able to also include the table comment, e.g. using a
> union (?) so the view will include the table name, an empty 'column'
> column, and the table comment.
>
> 2. I also want to be able to include the above union (if it is a union I
> need) for all tables across two schemas.
>
> What would be the sql for 1. and 2.?

Something like the following if I understand correctly:

8<------------------------
CREATE OR REPLACE VIEW metadata1 AS
SELECT
  n.nspname as schema_name,
  c.relname as table_name,
  a.attname as column_name,
  pg_catalog.col_description(c.oid, a.attnum) as comment
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE NOT n.nspname LIKE 'pg\_%'
AND NOT n.nspname = 'information_schema'
AND a.attnum > 0
AND c.relkind = 'r'
UNION ALL
SELECT
  n.nspname as schema_name,
  c.relname as table_name,
  '<table>' as column_name,
  pg_catalog.obj_description(c.oid) as comment
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE NOT n.nspname LIKE 'pg\_%'
AND NOT n.nspname = 'information_schema'
AND c.relkind = 'r'
ORDER BY 1,2,3
;

CREATE TABLE t1(id int, f1 text);
CREATE TABLE t2(id int, f2 text);
COMMENT ON TABLE t1 IS 'this is t1';
COMMENT ON COLUMN t1.id IS 'this is t1.id';
COMMENT ON COLUMN t1.f1 IS 'this is t1.f1';
COMMENT ON TABLE t2 IS 'this is t2';
COMMENT ON COLUMN t2.id IS 'this is t2.id';
COMMENT ON COLUMN t2.f2 IS 'this is t2.f2';

SELECT * FROM metadata1;
 schema_name | table_name | column_name |    comment
-------------+------------+-------------+---------------
 public      | t1         | <table>     | this is t1
 public      | t1         | f1          | this is t1.f1
 public      | t1         | id          | this is t1.id
 public      | t2         | <table>     | this is t2
 public      | t2         | f2          | this is t2.f2
 public      | t2         | id          | this is t2.id
(6 rows)
8<------------------------

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development