Re: Create view that retrieves both table and column comments

On 18 February 2016 at 22:01, Joe Conway <mail(at)joeconway(dot)com> wrote:

> On 02/18/2016 12:35 PM, Killian Driscoll 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:
>
> > 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.?
>
> Something like the following if I understand correctly:
>
> 8<------------------------
> CREATE OR REPLACE VIEW metadata1 AS
> SELECT
> n.nspname as schema_name,
> c.relname as table_name,
> a.attname as column_name,
> pg_catalog.col_description(c.oid, a.attnum) as comment
> FROM pg_catalog.pg_class c
> JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
> JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE NOT n.nspname LIKE 'pg\_%'
> AND NOT n.nspname = 'information_schema'
> AND a.attnum > 0
> AND c.relkind = 'r'
> UNION ALL
> SELECT
> n.nspname as schema_name,
> c.relname as table_name,
> '<table>' as column_name,
> pg_catalog.obj_description(c.oid) as comment
> FROM pg_catalog.pg_class c
> JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE NOT n.nspname LIKE 'pg\_%'
> AND NOT n.nspname = 'information_schema'
> AND c.relkind = 'r'
> ORDER BY 1,2,3
> ;
>

This works - thank you. I see in column "a.attname as column_name", there
are a few rows with data like '......pg.dropped.3.....': what are these?

>
> CREATE TABLE t1(id int, f1 text);
> CREATE TABLE t2(id int, f2 text);
> COMMENT ON TABLE t1 IS 'this is t1';
> COMMENT ON COLUMN t1.id IS 'this is t1.id';
> COMMENT ON COLUMN t1.f1 IS 'this is t1.f1';
> COMMENT ON TABLE t2 IS 'this is t2';
> COMMENT ON COLUMN t2.id IS 'this is t2.id';
> COMMENT ON COLUMN t2.f2 IS 'this is t2.f2';
>
> SELECT * FROM metadata1;
> schema_name | table_name | column_name | comment
> -------------+------------+-------------+---------------
> public | t1 | <table> | this is t1
> public | t1 | f1 | this is t1.f1
> public | t1 | id | this is t1.id
> public | t2 | <table> | this is t2
> public | t2 | f2 | this is t2.f2
> public | t2 | id | this is t2.id
> (6 rows)
> 8<------------------------
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>