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