Re: Create view that retrieves both table and column comments

Related to the sql to retrieve comments on table and columns:

CREATE OR REPLACE VIEW metadata2 AS
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;

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.

On 18 February 2016 at 22:32, Killian Driscoll <killian(dot)driscoll(at)ucd(dot)ie>
wrote:

>
>
> On 18 February 2016 at 22:31, Killian Driscoll <killian(dot)driscoll(at)ucd(dot)ie>
> wrote:
>
>>
>>
>> On 18 February 2016 at 22:22, Joe Conway <mail(at)joeconway(dot)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
>>>
>> Perfect, thank you,
>
>>
>>> --
>>> 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
>>
>

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