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

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

>
> CREATE TABLE tstfld (
>   id        serial       primary key,
>   fieldtype VARCHAR(128) NOT NULL CHECK(char_length(fieldtype) > 2),
>   length    SMALLINT     DEFAULT NULL,
>   chk       TEXT         DEFAULT NULL
> ) WITHOUT OID;
>
> INSERT INTO tstfld VALUES (
>   default,
>   'CHAR',
>   2,
>   E'CHECK((char_length(fieldtype) = 2) AND (fieldtype ~ ^\\d{2}::text))'
> );
>
>
> and I also don't understand why this don't work:
> SELECT char_length(SELECT chk FROM tstchk WHERE id=1);
> Can it only be use with a temp var into a proc?
>

What is exactly should that be doing? Don't you want:

SELECT char_length(chk) FROM tstchk WHERE id = 1;