pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to determine array size
5+ messages / 3 participants
[nested] [flat]
* how to determine array size
@ 2003-06-09 20:23 Forest Wilkinson <lyris-pg@tibit.com>
0 siblings, 2 replies; 5+ messages in thread
From: Forest Wilkinson @ 2003-06-09 20:23 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to determine array size
@ 2003-06-10 12:04 Rod Taylor <rbt@rbt.ca>
parent: Forest Wilkinson <lyris-pg@tibit.com>
1 sibling, 1 reply; 5+ messages in thread
From: Rod Taylor @ 2003-06-10 12:04 UTC (permalink / raw)
To: Forest Wilkinson <lyris-pg@tibit.com>; +Cc: pgsql-sql
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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to determine array size
@ 2003-06-10 12:32 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Forest Wilkinson <lyris-pg@tibit.com>
1 sibling, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2003-06-10 12:32 UTC (permalink / raw)
To: Forest Wilkinson <lyris-pg@tibit.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to determine array size
@ 2003-06-10 17:45 Forest Wilkinson <lyris-pg@tibit.com>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Forest Wilkinson @ 2003-06-10 17:45 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
>> 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.)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to determine array size
@ 2003-06-10 19:30 Rod Taylor <rbt@rbt.ca>
parent: Forest Wilkinson <lyris-pg@tibit.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2003-06-10 19:30 UTC (permalink / raw)
To: Forest Wilkinson <lyris-pg@tibit.com>; +Cc: pgsql-sql
> 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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-06-10 19:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-09 20:23 how to determine array size Forest Wilkinson <lyris-pg@tibit.com>
2003-06-10 12:04 ` Rod Taylor <rbt@rbt.ca>
2003-06-10 17:45 ` Forest Wilkinson <lyris-pg@tibit.com>
2003-06-10 19:30 ` Rod Taylor <rbt@rbt.ca>
2003-06-10 12:32 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
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