Create view that retrieves both table and column comments

From: Killian Driscoll <killian(dot)driscoll(at)ucd(dot)ie>
To: pgsql-novice(at)postgresql(dot)org
Subject: Create view that retrieves both table and column comments
Date: 2016-02-18 20:35:30
Message-ID: CAFTc7Ae5a27D8uuUG1mNHd_O7QGSsfsc-1wJ5OXFUdLFvHbroA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2016-02-18 20:45:59 Re: Create view that retrieves both table and column comments
Previous Message Jack Ort 2016-02-18 00:23:56 Slow Query Performance Using ogr_fdw on Windows 2012 R2 with PG 9.5