Re: Create view that retrieves both table and column comments

On Thu, Feb 18, 2016 at 1:35 PM, Killian Driscoll <killian(dot)driscoll(at)ucd(dot)ie>
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:
>
> 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.
>
> What would be the sql for 1. and 2.?
>

Yes, you will need to use UNION [ALL]

Write you table/table-comment query, adding a select-list entry like (
SELECT table_name AS table, '<n/a>'::text AS column​, [...] AS comment )
then

SELECT * metadata1
UNION ALL
SELECT * FROM <table-entry-query>

David J.