Create view that retrieves both table and column comments

Поиск
Список
Период
Сортировка
От Killian Driscoll
Тема Create view that retrieves both table and column comments
Дата
Msg-id CAFTc7Ae5a27D8uuUG1mNHd_O7QGSsfsc-1wJ5OXFUdLFvHbroA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Create view that retrieves both table and column comments
Re: Create view that retrieves both table and column comments
Список 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.?

В списке pgsql-novice по дате отправления:

Предыдущее
От: Jack Ort
Дата:
Сообщение: Slow Query Performance Using ogr_fdw on Windows 2012 R2 with PG 9.5
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Create view that retrieves both table and column comments