Thread: how to extract and use a string like a constraint?
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:
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?
--
Outside of a dog, a book is a man's best friend. Inside a dog it's too
dark to read. - Groucho Marx
"Jean-Yves F. Barbier" <12ukwn@gmail.com> wrote: > [...] > 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? You have to use parentheses around the "SELECT" query, i. e.: | SELECT char_length((SELECT chk FROM tstchk WHERE id=1)); Tim
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;
Le Wed, 07 Jul 2010 20:31:50 +0000, Tim Landscheidt <tim@tim-landscheidt.de> a écrit : > "Jean-Yves F. Barbier" <12ukwn@gmail.com> wrote: > > > [...] > > 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? > > You have to use parentheses around the "SELECT" query, > i. e.: > > | SELECT char_length((SELECT chk FROM tstchk WHERE id=1)); Thanks Tim (I tested many signs but this one) -- semper en excretus
On 7 July 2010 22:07, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > Le Wed, 7 Jul 2010 21:34:38 +0100, > Thom Brown <thombrown@gmail.com> a écrit : > >> 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 > > 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
Le Wed, 7 Jul 2010 22:16:09 +0100,
Thom Brown <thombrown@gmail.com> a écrit :
...
> 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?
Ok, I tried it but it returned an empty line.
My purpose is to be able to "add" field(s) to existing tables in an
OLAP/ROLAP manner (the article said so), so I've got 5 tables:
1) knows every tables in DB (ie: 'myschema.mytable'),
2) describes the field (the 1st one I talked about):
type ie: VARCHAR(16)
lenght ie: 16
chk ie: a string representing the constraint I want to apply to
the 'type' field,
3) gives a readeable name to the field (ie: 'Choose age') to be used by an
external pgm that'll read it and display in a window,
4) (optional) gives options (ie: '21-30','31-40')
5) contains data of the field and an IR toward the original table's row
number.
This way, I'll be able to easily add any number of fields to any table,
an original table's row that don't have a value in data table (5) means
it is NULL.
(well, re-reading myself I don't know if I used the right words:)
--
I'm not laughing with you, I'm laughing at you.