Обсуждение: getting comment about constraint

How do you retrieve a comment on a constraint?  For example, consider
the following table and comment:

 create table people (
    person_id     serial        primary key,
    uid           varchar(25)   not null,
    constraint uid_alphanumeric check (uid ~ '^[a-z0-9_]+$')
 );

 comment on constraint uid_alphanumeric on people
   is 'UID may only contain letters, numerals, and underscores';

That code code creates a constraint within the "people" table named
"uid_alphanumeric".  Furthermore, it creates a comment on that
constraint.

Given the name of the table and constraint, how would you retrieve the
comment?  "Chapter 9. Functions and Operators" says that
obj_description(object_oid, catalog_name) returns a comment given the
object's OID and class name.  How do I get the OID for the constraint?
It doesn't seem to be stored in pg_constraint.

On Sun, Jun 18, 2006 at 03:02:05PM -0700, canfieldsteve@hotmail.com wrote:
> Given the name of the table and constraint, how would you retrieve the
> comment?  "Chapter 9. Functions and Operators" says that
> obj_description(object_oid, catalog_name) returns a comment given the
> object's OID and class name.  How do I get the OID for the constraint?
> It doesn't seem to be stored in pg_constraint.

How did you determine that?  Are you aware that oid is a system
column that isn't ordinarily displayed?  The following works in
every version of PostgreSQL I checked (7.3.15, 7.4.13, 8.0.8, 8.1.4,
8.2devel):

SELECT obj_description(oid, 'pg_constraint')
FROM pg_constraint
WHERE conname = 'constraint_name'
  AND conrelid = 'table_name'::regclass;

Replace constraint_name and table_name with the actual names
(uid_alphanumeric and people in the example you gave).

--
Michael Fuhr