Thread: how to determine array size

how to determine array size

From
Forest Wilkinson
Date:
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?



Re: how to determine array size

From
Achilleus Mantzios
Date:
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



Re: how to determine array size

From
Rod Taylor
Date:
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

Re: how to determine array size

From
Forest Wilkinson
Date:
>> 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.)





Re: how to determine array size

From
Rod Taylor
Date:
> 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