0

is it possible to retreive the value of a column AND the descirption of each column? I want to see the description behind wach value.

Like:

SELECT 
    a.*,
    (SELECT 
        d.description
    FROM
        information_schema.COLUMNS AS c
        LEFT JOIN pg_description as d ON 
        d.objsubid = c.ordinal_position
    WHERE
        TABLE_NAME = <a.tablename> AND column_name = <colname>) as description
FROM table as a
WHERE true;

The table should by look like this:

id: serial 'no description';
col1: int2 'Column One';
col2: int2 'Column Two';

Content:

1, 10, 11
2, 20, 21
3, 30, 31

The result should look like this:

1, 10 (column one), 11 (column two)
2, 20 (column one), 21 (column two)
3, 30 (column one), 31 (column two)

Best regards

Christian

0

The only way I can think of is something like this:

select t.id, x.remarks ->> 'id',
       t.col1, x.remarks ->> 'col1',
       t.col2, x.remarks ->> 'col2'
from the_table t
 join lateral ( 
    select jsonb_object_agg(a.attname, col_description(a.attrelid, a.attnum)) as remarks
    from pg_attribute a 
    where a.attrelid = t.tableoid
    and a.attnum > 0
 ) x on true

The inner query aggregates all column descriptions of the table into a single JSON value that can be accessed easily in the outer query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.