How to retrieve the comment for a constraint?
Hallo all,
using at least PostgreSQL 7.4.2, one can use the command
COMMENT ON CONSTRAINT <constraint_name> ON <table_name>;
to document a constraint defined via
CONSTRAINT <constraint_name> ...
in the context of a table.
Now, imagine you know the name of a constraint or all of them for a given
table, e.g. destilled via
SELECT *
FROM information_schema.table_constraints
WHERE table_name = '<table_name>';
How can I get the comment for each of these constraints?
I mean, I can query pg_catalog.pg_description with
SELECT *
FROM pg_description
WHERE description ~ '<Begin of comment> *';
and get the tuple
objoid | classoid | objsubid | description
with the string I want to extract. I can see that it is there.
In short... Given names of schema, table and constraint, how can I get the
description out of pg_catalog.pg_description?
Do I overlook something in the forest of system catalogs, its OIDs, the
information schema...?
Thank you!
Best regards,
Wolfgang
Wolfgang Drotschmann <drotschm@fgan.de> writes:
Now, imagine you know the name of a constraint or all of them for a given
table, e.g. destilled via
SELECT *
FROM information_schema.table_constraints
WHERE table_name = '<table_name>';
How can I get the comment for each of these constraints?
Something like this...
regression=# alter table foo add constraint bar check(id > 0);
ALTER TABLE
regression=# comment on constraint bar on foo is 'check its positive';
COMMENT
regression=# select obj_description(oid, 'pg_constraint') from pg_constraint where conname = 'bar' and conrelid = 'foo'::regclass;
obj_description
--------------------
check its positive
(1 row)
You could join to pg_description explicitly instead of using
obj_description(), and/or join to pg_class instead of using regclass.
regards, tom lane