On 7 July 2010 21:13, Jean-Yves F. Barbier <12ukwn@gmail.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;