Re: how to extract and use a string like a constraint?

On 7 July 2010 22:07, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:
> Le Wed, 7 Jul 2010 21:34:38 +0100,
> Thom Brown <thombrown(at)gmail(dot)com> a écrit :
>
>> On 7 July 2010 21:13, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:
>> > Hi listers,
>> >
>> > I've got a table that describes a field and its constraint, but I don't
>> > have any clue about how to extract and use this constraint as if it was
>> > on a regular field line:
>>
>> Not sure what you mean there.  Do you mean you want to get the textual
>> representation of an existing constraint?
>>
>> Like:
>>
>> SELECT r.conrelid::regclass as "table_name", r.conname as
>> "constraint_name", pg_catalog.pg_get_constraintdef(r.oid, true) as
>> "constraint"
>> FROM pg_catalog.pg_constraint r
>> WHERE r.conrelid::regclass = 'tstfld'::regclass AND r.contype = 'c'
>> ORDER BY 1
>
> Whao, I'm not tough enough at this time to fully understand that!
>
> "table_name"       is obvious,
> "constraint_name"  I guess this name's not important as its scope is limited
>                    to the query? (maybe "const01")
> "constraint"       does it mean the chk field content?
>

Given your example, this would return:

table_name | constraint_name | constraint
------------+------------------------+------------------------------------------
tstfld | tstfld_fieldtype_check | CHECK (char_length(fieldtype::text) > 2)
(1 row)

I just aliased the columns to clarify what each contained. The
constraint_name is the object name of the constraint that was created
when you created the table. The constraint is the actual constraint.

However, this might not be what you're after. Could you clarify what
you're trying to do?

Thanks

Thom