Обсуждение: Create view that retrieves both table and column comments

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.?

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

On 18 February 2016 at 22:22, Joe Conway <mail@joeconway.com> wrote:

On 02/18/2016 01:15 PM, Killian Driscoll wrote:
> On 18 February 2016 at 22:01, Joe Conway wrote:
>     WHERE NOT n.nspname LIKE 'pg\_%'
>     AND NOT n.nspname = 'information_schema'
>     AND a.attnum > 0
>     AND c.relkind = 'r'
>     UNION ALL

> 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?

Those are columns that have been dropped after the table was created.
You could filter those out by adding

  AND NOT a.attisdropped

into the WHERE clause before the UNION ALL

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
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll

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