Re: Create view that retrieves both table and column comments - Mailing list pgsql-novice

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.

HTH,

Joe

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