Re: Create view that retrieves both table and column comments

On 21 February 2016 at 17:52, Joe Conway <mail(at)joeconway(dot)com> wrote:

> On 02/20/2016 11:59 PM, Killian Driscoll wrote:
> > For the column 4 of the view (col_description...AS comment), the output
> > includes rows such as "(Date Created) Date image created" - which is the
> > comment that begins with the column's 'display name' in brackets,
> > followed by its description.
> >
> > What sql can I use within the original sql, or via a second view to
> > extract the first part of the original column 4 that is in brackets and
> > have that appear as another column (a new fourth column, with the
> > original fourth now in fifth postion) in the view - the end result being
> > a view with five columns, e.g. schema_name, table_name, column_name,
> > column_display_name, comment.
> >
> > Most of the col_description rows begin with data (the display name) in
> > brackets that I want to extract, but some don't, so the sql would need a
> > clause to ignore those rows with no brackets.
>
> Assuming this data:
> 8<---------------------
> 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 '(t1 Identifier) this is t1.id';
> COMMENT ON COLUMN t1.f1 IS '(Label for f1) this is t1.f1';
> COMMENT ON TABLE t2 IS 'this is t2';
> COMMENT ON COLUMN t2.id IS '(t2 Identifier) this is t2.id';
> COMMENT ON COLUMN t2.f2 IS '(Label for f2) this is t2.f2';
>
> select * from metadata2;
> schema_name | table_name | column_name | comment
> -------------+------------+-------------+-------------------------------
> public | t1 | <table> | this is t1
> public | t1 | f1 | (Label for f1) this is t1.f1
> public | t1 | id | (t1 Identifier) this is t1.id
> public | t2 | <table> | this is t2
> public | t2 | f2 | (Label for f2) this is t2.f2
> public | t2 | id | (t2 Identifier) this is t2.id
> (6 rows)
>
> SELECT schema_name,
> table_name,
> column_name,
> CASE WHEN left(comment, 1) = '(' THEN
> trim(split_part(comment, ')', 1),'(')
> ELSE
> NULL
> END AS label,
> CASE WHEN left(comment, 1) = '(' THEN
> trim(split_part(comment, ')', 2))
> ELSE
> comment
> END AS comment
> FROM metadata2;
>
> schema_name | table_name | column_name | label | comment
> -------------+------------+-------------+---------------+---------------
> public | t1 | <table> | | this is t1
> public | t1 | f1 | Label for f1 | this is t1.f1
> public | t1 | id | t1 Identifier | this is t1.id
> public | t2 | <table> | | this is t2
> public | t2 | f2 | Label for f2 | this is t2.f2
> public | t2 | id | t2 Identifier | this is t2.id
> (6 rows)
> 8<---------------------
>
> There are certainly other ways to do this, and this might not
> necessarily be the best, but it seems to do what you want. It would be
> cleaner if you have control over the format of the column comments to
> make them more easily, and perhaps more reliably, parsable.
>
> Come to think of it, probably it would be simpler/cleaner to do this
> with regex functions:
>
> 8<---------------------
> SELECT
> schema_name,
> table_name,
> column_name,
> substring(comment from '\((.*?)\)') AS label,
> trim(regexp_replace(comment, '\(.*?\)', '')) AS comment
> FROM metadata2;
> schema_name | table_name | column_name | label | comment
> -------------+------------+-------------+---------------+---------------
> public | t1 | <table> | | this is t1
> public | t1 | f1 | Label for f1 | this is t1.f1
> public | t1 | id | t1 Identifier | this is t1.id
> public | t2 | <table> | | this is t2
> public | t2 | f2 | Label for f2 | this is t2.f2
> public | t2 | id | t2 Identifier | this is t2.id
> (6 rows)
> 8<---------------------
>
> Obviously both of these would need to be tested carefully with your
> actual data.
>

This worked perfectly and a sub-select to combine these two views (which
was answered by Joe Conway, but I accidentally replied to his email, not
this list, so I'll post his final solution here) is:

CREATE OR REPLACE VIEW metadata2 AS
SELECT
schema_name,
table_name,
column_name,
substring(comment from '\((.*?)\)') AS label,
trim(regexp_replace(comment, '\(.*?\)', '')) AS comment
FROM
(
SELECT n.nspname AS schema_name,
c.relname AS table_name,
a.attname AS column_name,
col_description(c.oid, a.attnum::integer) AS comment
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT n.nspname ~~ 'pg\_%'::text AND NOT n.nspname =
'information_schema'::name AND NOT a.attisdropped AND a.attnum > 0 AND
c.relkind = 'r'::"char"
UNION ALL
SELECT n.nspname AS schema_name,
c.relname AS table_name,
'<table>'::name AS column_name,
obj_description(c.oid) AS comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT n.nspname ~~ 'pg\_%'::text AND NOT n.nspname =
'information_schema'::name AND c.relkind = 'r'::"char"
ORDER BY 1, 2, 3
) AS ss;

Thanks again!

>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

--
Killian DriscoIl
IRC Postdoctoral Fellow
UCD School of Archaeology
University College Dublin

academia.edu/KillianDriscoll <https://ucd.academia.edu/KillianDriscoll>
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll