Retrieving Comments from a PostGres DB

Question

Hi There...

Im running a project on a PostGres database and need to retrieve the comments on columns within the DB to be used as table headings and such. I have seen that there are a couple of built in functions (pg_description and col_description) but i haven't been able to find examples on how to use them and playing around with them has proved pretty futile.

So I was wondering if any has been able to do this before and if so, How???

Thanks

Answer 1

Ok, so i worked it out to degree...

select col_description(table id, column number)...

ie: select col_description(36698,2);

That worked, but is there an easier way to do this maybe bringing all the comments on all the columns and using the table name instead of the oid???

Answer 2

It all works by oid,

mat=> SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname = 'customers';
  oid  
-------
 23208
(1 row)

Now, I have the oid for that table, so I can ask :

mat=> select pg_catalog.obj_description(23208);
  obj_description  
-------------------
 Customers
(1 row)

Then, I can ask for the description of the fourth column :

mat=> select pg_catalog.col_description(23208,4);
             col_description             
-----------------------------------------
 Customer codes, CHS, FACTPOST, POWER...
(1 row)

If you want to know which queries does psql run when you do \dt+ or \d+ customers, just launche it with -E.

Answer 3

Cool, that works to bring up a single comment, but is there a way to bring up all the comments from all the columns without multiple select statements or a loop?

And how do you launch this with a statement:

If you want to know which queries does psql run when you do \dt+ or \d+ customers, just launche it with -E.

Thanks

Answer 4

I asked a similar question about Postgresql comments last month. If you dig through that, you'll come across some Perl code over on my blog that automates the process of extracting a comment.

To pull out the column names of a table, you can use something like the following:

select
     a.attname  as "colname"
    ,a.attrelid as "tableoid"
    ,a.attnum   as "columnoid"
from
    pg_catalog.pg_attribute a
    inner join pg_catalog.pg_class c on a.attrelid = c.oid
where
        c.relname = 'mytable' -- better to use a placeholder
    and a.attnum > 0
    and a.attisdropped is false
    and pg_catalog.pg_table_is_visible(c.oid)
order by a.attnum

You can then use the tableoid,columnoid tuple to extract the comment of each column (see my question).

Answer 5

This works for me using the PostBooks 3.2.2 DB:

select cols.column_name,
(select pg_catalog.obj_description(oid) from pg_catalog.pg_class c where c.relname=cols.table_name) as table_comment
,(select pg_catalog.col_description(oid,cols.ordinal_position::int) from pg_catalog.pg_class c where c.relname=cols.table_name) as column_comment
from information_schema.columns cols
where cols.table_catalog='postbooks' and cols.table_name='apapply'

Regards, Sylnsr

Answer 6

This answer is a little late, but it popped up on a google search I did to research this problem. We only needed Table descriptions, but the method would be the same for columns. The column descriptions are in the pg_description table also, referenced by objoid.

Add this view:

CREATE OR REPLACE VIEW our_tables AS 
 SELECT c.oid, n.nspname AS schemaname, c.relname AS tablename, d.description,
   pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", 
   c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
   LEFT JOIN pg_description d ON c.oid = d.objoid
  WHERE c.relkind = 'r'::"char";

ALTER TABLE our_tables OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE our_tables TO postgres;
GRANT SELECT ON TABLE our_tables TO public;

Then run:

SELECT tablename, description FROM our_tables WHERE schemaname = 'public'

The view is a modified version of the pg_tables view which adds in the description column. You could also monkey around with the view definition to make it a single query.