From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Killian Driscoll <killian(dot)driscoll(at)ucd(dot)ie>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Create view that retrieves both table and column comments |
Date: | 2016-02-18 21:01:55 |
Message-ID: | 56C63143.5070405@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
;
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
From | Date | Subject | |
---|---|---|---|
Next Message | Killian Driscoll | 2016-02-18 21:15:41 | Re: Create view that retrieves both table and column comments |
Previous Message | David G. Johnston | 2016-02-18 20:45:59 | Re: Create view that retrieves both table and column comments |