What would it take to make the array iterator functions a part of the
standard base? (contrib/array)

A number of people want this type of functionality (value = [ any
value of array ], and value = [ all values of array ] ).

The license needs to be changed (with authors permission), but other
than that?

-- README BELOW --
This loadable module defines a new class of functions which take
an array and a scalar value, iterate a scalar operator over the
elements of the array and the value, and compute a result as
the logical OR or AND of the iteration results.
For example array_int4eq returns true if some of the elements
of an array of int4 is equal to the given value:

 array_int4eq({1,2,3}, 1)  -->  true
 array_int4eq({1,2,3}, 4)  -->  false

If we have defined T array types and O scalar operators we can
define T x O x 2 array functions, each of them has a name like
"array_[all_]<basetype><operation>" and takes an array of type T
iterating the operator O over all the elements. Note however
that some of the possible combination are invalid, for example
the array_int4_like because there is no like operator for int4.

We can then define new operators based on these functions and use
them to write queries with qualification clauses based on the
values of some of the elements of an array.
For example to select rows having some or all element of an array
attribute equal to a given value or matching a regular expression:

 create table t(id int4[], txt text[]);

 -- select tuples with some id element equal to 123
 select * from t where t.id *= 123;

 -- select tuples with some txt element matching '[a-z]'
 select * from t where t.txt *~ '[a-z]';

 -- select tuples with all txt elements matching '^[A-Z]'
 select * from t where t.txt[1:3] **~ '^[A-Z]';

The scheme is quite general, each operator which operates on a base
type
can be iterated over the elements of an array. It seem to work well
but
defining each new operators requires writing a different C function.
Furthermore in each function there are two hardcoded OIDs which
reference
a base type and a procedure. Not very portable. Can anyone suggest a
better and more portable way to do it ?

See also array_iterator.sql for an example on how to use this module.
--
Rod Taylor



Mon, 15 Apr 2002 13:39:35 -0400
"Rod Taylor" <rbt@zort.ca>
Array Iterator functions
"Rod Taylor" <rbt@zort.ca> writes:
> What would it take to make the array iterator functions a part of the
> standard base? (contrib/array)

To me, the main problem with contrib/array is that it doesn't scale:
you need more C functions for every array datatype you want to support.

At the very least it needs a way to avoid more per-datatype C code.
The per-datatype operator definitions are annoying too, but perhaps
not quite as annoying... one could imagine CREATE TYPE automatically
adding those along with the array type itself.

I'm not sure what it would take to avoid the per-datatype C code.
Clearly we want something like array_in/array_out, but how does the
extra information get to these functions?

It would also be good to have some idea of whether we could ever hope to
index queries using these functions.  The GIST stuff might provide that,
or it might not.  I don't insist that this work on day one, but I'd like
to see a road map, just to be sure that we are not shooting ourselves in
the foot by standardizing a not-quite-index-compatible definition.

			regards, tom lane

Mon, 15 Apr 2002 15:17:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Array Iterator functions 