Date: Tue, 10 Jun 2003 10:32:00 -0200 (GMT+2)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Forest Wilkinson <lyris-pg@tibit.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to determine array size
In-Reply-To: <nqh9ev007tlivjj1nnakco0ci541jucse5@4ax.com>
Message-ID: <Pine.LNX.4.44.0306101020390.797-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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