Thread: How can I select a comment on a column in a query?

Hello,

I have been going through the manuals for couple of hours looking for a
way to select a comment on a column in a query. Can it be done (and used
with php)?

Thanks for any advice

Roman Dergam

Hi Roman,

Comments are stored in the system catalog table pg_description.  To select a
column comment ("description" in pg_description) you need the specify the
objoid for the table ("relfilenode" from pg_class for the table in question)
and the column number ("objsubid").

test=# select description from pg_description where objoid = 306461 and
objsubid = 0;

            description
-----------------------------------
 Weight Units~Unit~unitdescription
(1 row)

HTH,
George

----- Original Message -----
From: "A u r k a" <aurka@centrum.cz>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, January 15, 2004 9:04 AM
Subject: [NOVICE] How can I select a comment on a column in a query?


> Hello,
>
> I have been going through the manuals for couple of hours looking for a
> way to select a comment on a column in a query. Can it be done (and used
> with php)?
>
> Thanks for any advice
>
> Roman Dergam
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

George Weaver <gweaver@shaw.ca> writes:
> test=# select description from pg_description where objoid = 306461 and
> objsubid = 0;

Also, there is a col_description() function that encapsulates this
query.  I'd recommend col_description() and its sibling obj_description()
in preference to examining the pg_description catalog directly.  See
"Comment Information Functions" in
http://www.postgresql.org/docs/7.4/static/functions-misc.html

Here's a simple example:

regression=# create table mytab (mycol int);
CREATE TABLE
regression=# comment on column mytab.mycol is 'my comment';
COMMENT
regression=# select col_description('mytab'::regclass, 1);
 col_description
-----------------
 my comment
(1 row)

(I'm using the regclass datatype as a substitute for an explicit
lookup in pg_class to get the OID of the table.  This feature is
new since 7.3 or thereabouts.)

            regards, tom lane