create or replace view metadata1 as SELECT cols.table_name as table, cols.column_name as column, ( 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 = 'db1' AND cols.table_schema = 'schema1' AND cols.table_name = 'table1';
I'd like to do two additional things.
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.