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
Date: Tue, 10 Jun 2003 10:32:00 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0306101020390.797-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <nqh9ev007tlivjj1nnakco0ci541jucse5@4ax.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, lyris-pg@tibit.com
Subject: Re: how to determine array size
In-Reply-To: <Pine.LNX.4.44.0306101020390.797-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox