Re: Get comment

On Mon, Dec 13, 2004 at 08:56:51AM -0300, Douglas wrote:

> Please giveme an example! My table:
>
> CREATE TABLE foo
> (
> id int2 NOT NULL,
> name varchar(30) NOT NULL,
> CONSTRAINT pk_foo PRIMARY KEY (id)
> )
> WITH OIDS;
> ALTER TABLE foo OWNER TO postgres;
> COMMENT ON TABLE is IS 'Generic table';

The above line has a mistake -- the table name should be "foo"
instead of "is".

> COMMENT ON COLUMN foo.id IS 'Identification of registry';
> COMMENT ON COLUMN foo.name IS 'Name of registry';
>
> Now I have in my application two selects ('select table_name from pg_tables'

If you're selecting from pg_tables then the field is tablename, not
table_name. It is table_name, however, in information_schema.tables.

> / 'select column_name from information_schema.columns') for get the names of
> columns and tables, but I hope get the coments....

As I suggested, run "psql -E" and look at the output of "\d+" and
"\d+ foo" -- that'll show how psql queries for tables, columns, and
comments. Here are some simple examples:

SELECT n.nspname, c.relname,
obj_description(c.oid, 'pg_class') AS comment
FROM pg_class AS c JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY n.nspname, c.relname;

SELECT attname, col_description(attrelid, attnum) AS comment
FROM pg_attribute
WHERE attrelid = 'foo'::regclass AND attnum > 0 AND NOT attisdropped
ORDER BY attnum;

Study the "System Catalogs" chapter in the documentation to learn
more about querying the tables that describe your database objects.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/