Thread: how to determine array size
I need to enumerate the constraints on any given column in a table, so I'm examining pg_constraint to get the relevant information. The conkey array contains a list of constrained columns, and although I am able to check conkey[1] for constraints on a single column, I would like to properly handle multi-column constraints. How do I determine the size of the conkey array? I haven't found any field that looks like it contains the number of values in conkey. Do I have to check each element of the array sequentially, until I get a NULL value from one of them? (Section 5.12 of the User's Guide seems to forbid this: "A limitation of the present array implementation is that individual elements of an array cannot be SQL null values.") Moreover, that method doesn't give me a nice way of selecting all constraints on a specific column, as I would have to write clauses like this: ... WHERE conkey[1] = blah OR conkey[2] = blah OR conkey[3] = blah OR conkey[4] = blah ... Can somone offer a better way?
On Mon, 9 Jun 2003, Forest Wilkinson wrote:
> I need to enumerate the constraints on any given column in a table, so
> I'm examining pg_constraint to get the relevant information. The
> conkey array contains a list of constrained columns, and although I am
> able to check conkey[1] for constraints on a single column, I would
> like to properly handle multi-column constraints.
>
> How do I determine the size of the conkey array? I haven't found any
> field that looks like it contains the number of values in conkey. Do
> I have to check each element of the array sequentially, until I get a
> NULL value from one of them? (Section 5.12 of the User's Guide seems
> to forbid this: "A limitation of the present array implementation is
> that individual elements of an array cannot be SQL null values.")
> Moreover, that method doesn't give me a nice way of selecting all
> constraints on a specific column, as I would have to write clauses
> like this:
>
> ... WHERE conkey[1] = blah OR conkey[2] = blah OR conkey[3] = blah OR
> conkey[4] = blah ...
>
> Can somone offer a better way?
Well if you are willing to extend contrib package intarray
to something like smallintarray
you could simply do
SELECT conname from pg_constraint where conrelid=<your table oid> and
'{blah}' ~ conkey;
Or as a quick solution create your own function
boolean isinarr(smallint,smallint[])
that performs this task, and do
SELECT conname from pg_constraint where conrelid=<your table oid> and
isinarr(blah,conkey);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr
On Mon, 2003-06-09 at 16:23, Forest Wilkinson wrote: > I need to enumerate the constraints on any given column in a table, so > I'm examining pg_constraint to get the relevant information. The > conkey array contains a list of constrained columns, and although I am > able to check conkey[1] for constraints on a single column, I would > like to properly handle multi-column constraints. You may have an easier time dealing with pg_get_constraintdef() than trying to get the info from the source. The above function outputs the necessary SQL to rebuild the constraint, and is used by pg_dump. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
>> I need to enumerate the constraints on any given column in a table, so >> I'm examining pg_constraint to get the relevant information. The >> conkey array contains a list of constrained columns, and although I am >> able to check conkey[1] for constraints on a single column, I would >> like to properly handle multi-column constraints. > >You may have an easier time dealing with pg_get_constraintdef() than >trying to get the info from the source. Is pg_get_constraintdef() documented somewhere? I'd like to know it's arguments, return format, and whether it will be supported in future postgres releases. From what I see in pg_dump.c, it appears to accept an oid from the pg_constraint table, and only work with foreign key constraints. True? That might be useful in some special-case code, but I really want a method that will work with all types of constraint. (The idea here is to enumerate all constraints on a table, along with their names, types, and constrained columns.)
> Is pg_get_constraintdef() documented somewhere? I'd like to know it's > arguments, return format, and whether it will be supported in future > postgres releases. Support for it will improve, and it'll be around for a few releases anyway. > >From what I see in pg_dump.c, it appears to accept an oid from the > pg_constraint table, and only work with foreign key constraints. > True? That might be useful in some special-case code, but I really > want a method that will work with all types of constraint. (The idea The other types of constraints have been added in 7.4. In fact, it's the only way to fetch an accurate CHECK constraint in 7.4. Miscellaneous functions are documented at: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-misc.html -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc