> 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 ;
This works - thank you. I see in column "a.attname as column_name", there are a few rows with data like '......pg.dropped.3.....': what are these?
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