
So where are the new array functions and syntaces documented?

Specifically I want to know how to replace my int_array_aggregate(int) and
int_array_enum(_int) calls. And how to replace my "arr *= n" calls too. 

I think these are supposed be "ALL my_array" and "n = ANY myarray" or
something like that?

-- 
greg


28 Aug 2003 13:13:09 -0400
Greg Stark <gsstark@mit.edu>
New array functions
Greg Stark wrote:
> So where are the new array functions and syntaces documented?

Mainly here:
http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
http://developer.postgresql.org/docs/postgres/arrays.html
http://developer.postgresql.org/docs/postgres/functions-array.html
http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154

> Specifically I want to know how to replace my int_array_aggregate(int) and
> int_array_enum(_int) calls.

I have no idea what those are -- are they from contrib?

You can create an aggregate to turn arbitrary datatype elements into 
arrays like this:

CREATE AGGREGATE array_aggregate
(
   BASETYPE = anyelement,
   SFUNC = array_append,
   STYPE = anyarray,
   INITCOND = '{}'
);

-- silly example, but what the heck ;-)
regression=# select attrelid, array_aggregate(attnum) from pg_attribute 
where attnum > 0 and attnum < 5 group by attrelid limit 3;
  attrelid | array_aggregate
----------+-----------------
     16639 | {1}
     16638 | {1}
     17022 | {1,2,3,4}
(3 rows)


If int_array_enum() is supposed to take '{1,2,3}' and produce three 
rows, that function was proposed but rejected. Subsequently Peter 
Eisentraut pointed out a SQL99 syntax that does this, but I did not get 
it done for 7.4. Perhaps for 7.5.

> And how to replace my "arr *= n" calls too. 

See:
http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154

regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM 
pg_shadow s, pg_group g WHERE s.usesysid = any (g.grolist);
  grosysid | groname | usesysid | usename
----------+---------+----------+----------
       102 | admins  |        1 | postgres
       100 | grp1    |      100 | user1
       101 | grp2    |      100 | user1
       100 | grp1    |      101 | user2
       100 | grp1    |      102 | user3
       101 | grp2    |      102 | user3
       102 | admins  |      103 | john
(7 rows)


HTH,

Joe


Thu, 28 Aug 2003 11:15:05 -0700
Joe Conway <mail@joeconway.com>
Re: New array functions
Joe Conway <mail@joeconway.com> writes:

> Greg Stark wrote:
> > So where are the new array functions and syntaces documented?
> 
> Mainly here:
> http://developer.postgresql.org/docs/postgres/arrays.html

excellent. thank you.

> > Specifically I want to know how to replace my int_array_aggregate(int) and
> > int_array_enum(_int) calls.
> 
> I have no idea what those are -- are they from contrib?

indeed in contrib/intagg

> You can create an aggregate to turn arbitrary datatype elements into arrays
> like this:
> 
> CREATE AGGREGATE array_aggregate
> (
>    BASETYPE = anyelement,
>    SFUNC = array_append,
>    STYPE = anyarray,
>    INITCOND = '{}'
> );

Hm, perhaps there should be a standard name for this, rather than have
everyone's code do their own thing.

> If int_array_enum() is supposed to take '{1,2,3}' and produce three rows, that
> function was proposed but rejected. Subsequently Peter Eisentraut pointed out a
> SQL99 syntax that does this, but I did not get it done for 7.4. Perhaps for 7.5.

That's exactly what it does. Hm, I guess I misinterpreted that post. Hm I have
some work to do.

Thanks. And thanks a LOT for doing the work, it'll makes a big difference
and make arrays much more practical to use.

-- 
greg


28 Aug 2003 14:45:22 -0400
Greg Stark <gsstark@mit.edu>
Re: New array functions
Joe Conway <mail@joeconway.com> writes:
> Greg Stark wrote:
>> And how to replace my "arr *= n" calls too. 

> See:
> http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154

That reminds me --- contrib/array is definitely obsolete now, and there
may be parts of the other contrib array- and aggregate-related modules
that are obsoleted by Joe's recent work.

I would like to kill contrib/array for 7.4, because it's one of the
GPL'd contrib modules that I was tasked to get rid of some time ago.

What I'm thinking of doing is removing the code, and replacing the
README with a note explaining how to convert contrib/array queries to
use the new mainstream syntaxes.  That will give contrib/array users
a clue what they're supposed to do.  In a release or three the README
could go away too.

Comments, objections?

Also, does anyone want to look for possible dead code in intagg and
so on?

			regards, tom lane

Thu, 28 Aug 2003 15:13:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New array functions 


> See:
> http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154
> 
> regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM pg_shadow
> s, pg_group g WHERE s.usesysid = any (g.grolist);


These forms below are all equivalent, right? 

If so ideally they would all be converted to an equivalent form and therefore
produce the same plan. I guess I'm wishing for a pony though. But I think
currently I'm stuck with the worst of these and I don't see any way of
escaping to the better plans.

Incidentally, "HashAggregate"?! Based on the earlier discussion on this I
would have expected that line to read "Materialize"


slo=> explain select * from store_location where store_location_id in (1,2,3);
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using store_location_pkey, store_location_pkey, store_location_pkey on store_location  (cost=0.00..17.74 rows=3 width=523)
   Index Cond: ((store_location_id = 1) OR (store_location_id = 2) OR (store_location_id = 3))
(2 rows)



slo=> explain select * from store_location where store_location_id in (select 1 union all select 2 union all select 3);
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.10..17.86 rows=3 width=523)
   ->  HashAggregate  (cost=0.10..0.10 rows=3 width=4)
         ->  Subquery Scan "IN_subquery"  (cost=0.00..0.09 rows=3 width=4)
               ->  Append  (cost=0.00..0.06 rows=3 width=0)
                     ->  Subquery Scan "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     ->  Subquery Scan "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Index Scan using store_location_pkey on store_location  (cost=0.00..5.91 rows=1 width=523)
         Index Cond: (store_location.store_location_id = "outer"."?column?")
(12 rows)



slo=> explain select * from store_location where store_location_id = any (array[1,2,3]);
                             QUERY PLAN                              
---------------------------------------------------------------------
 Seq Scan on store_location  (cost=0.00..825.75 rows=5954 width=523)
   Filter: (store_location_id = ANY ('{1,2,3}'::integer[]))
(2 rows)




-- 
greg


28 Aug 2003 16:16:07 -0400
Greg Stark <gsstark@mit.edu>
Re: New array functions
Tom Lane wrote:
> What I'm thinking of doing is removing the code, and replacing the
> README with a note explaining how to convert contrib/array queries to
> use the new mainstream syntaxes.  That will give contrib/array users
> a clue what they're supposed to do.  In a release or three the README
> could go away too.

I have no objection to removing it now, but previously I think you 
agreed with Bruce's comment that we should leave it intact (but 
deprecated) for 7.4, and remove in 7.5.

> Also, does anyone want to look for possible dead code in intagg and
> so on?

I did a quick review back in July.

IIRC, intagg could be functionally replaced with the aggregate 
definition that I posted, except that intagg is probably a fair bit 
better performance (I didn't actually test), in that it accumulates the 
array in backend memory and just pushes pointers around as int4's. I've 
thought that a safer implementation would be needed to fold it into the 
backend (maybe using hashes keyed with the pointer?), but in any case 
that's a 7.5 thing.

Also IIRC there were some functions in intarray that overlap the new 
backend functionality, but much of it (i.e. using GIST to index into 
large arrays) is not.

I'll try to review them again and make a recommendation in the next 
couple of days, but it might be a stretch because I'm trying to tie up 
lots of loose ends in preparation for a trip next week.

Joe


Thu, 28 Aug 2003 13:51:09 -0700
Joe Conway <mail@joeconway.com>
Re: New array functions
Joe Conway kirjutas N, 28.08.2003 kell 21:15:
> Greg Stark wrote:
> > Specifically I want to know how to replace my int_array_aggregate(int) and
> > int_array_enum(_int) calls.
> 
> I have no idea what those are -- are they from contrib?
> 
> You can create an aggregate to turn arbitrary datatype elements into 
> arrays like this:
> 
> CREATE AGGREGATE array_aggregate
> (
>    BASETYPE = anyelement,
>    SFUNC = array_append,
>    STYPE = anyarray,
>    INITCOND = '{}'
> );

Any idea of performance - is this array_aggregate(anyelement) faster,
slower or about same than int_array_aggregate(int) ?

> If int_array_enum() is supposed to take '{1,2,3}' and produce three 
> rows, that function was proposed but rejected. Subsequently Peter 
> Eisentraut pointed out a SQL99 syntax that does this, but I did not get 
> it done for 7.4. Perhaps for 7.5.

So we got to keep intagg at least until 7.5 ...

-----------
Hannu


Thu, 28 Aug 2003 23:52:32 +0300
Hannu Krosing <hannu@tm.ee>
Re: New array functions
Joe Conway <mail@joeconway.com> writes:
> I have no objection to removing it now, but previously I think you 
> agreed with Bruce's comment that we should leave it intact (but 
> deprecated) for 7.4, and remove in 7.5.

Did we discuss this already?  I'd forgotten.

In any case, the module isn't visibly deprecated at the moment.
If the idea is to avoid blindsiding its users, then we definitely
must mark it as slated for removal, and provide some docs about
how to replace it.

			regards, tom lane

Thu, 28 Aug 2003 17:05:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New array functions 
Hannu Krosing wrote:
> Any idea of performance - is this array_aggregate(anyelement) faster,
> slower or about same than int_array_aggregate(int) ?

I haven't tested, but I'd guess for an array of any significant length 
int_array_aggregate() is faster (see my other post). That's one of the 
reasons I haven't advocated deprecating intagg yet.

Joe


Thu, 28 Aug 2003 14:08:52 -0700
Joe Conway <mail@joeconway.com>
Re: New array functions
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> 
>>I have no objection to removing it now, but previously I think you 
>>agreed with Bruce's comment that we should leave it intact (but 
>>deprecated) for 7.4, and remove in 7.5.
> 
> Did we discuss this already?  I'd forgotten.
> 
> In any case, the module isn't visibly deprecated at the moment.
> If the idea is to avoid blindsiding its users, then we definitely
> must mark it as slated for removal, and provide some docs about
> how to replace it.
> 

I can't find it in the archives for some reason, but here was the exchange:

Tom Lane wrote:
 > Bruce Momjian <pgman@candle.pha.pa.us> writes:
 >
 >>Joe Conway wrote:
 >>
 >>>I do agree that it makes contrib/array unnecessary. I was going to
 >>>suggest we remove that if this was committed.
 >
 >>Good idea.
 >
 > We could do that, but it might be more friendly to just mark it as
 > deprecated for one release cycle before zapping it.  That'd give
 > people who use it some time to convert over.

So I guess since it was actually you who objected, you have the right to 
change your mind ;-)

Joe


Thu, 28 Aug 2003 14:44:26 -0700
Joe Conway <mail@joeconway.com>
Re: New array functions
Greg Stark <gsstark@mit.edu> writes:
> Incidentally, "HashAggregate"?! Based on the earlier discussion on this I
> would have expected that line to read "Materialize"

It's using a grouped aggregation node to implement a UNIQUE filter, so
that it can replace the "WHERE foo IN (subselect)" by a straight join.
Of course in this case the uniqueness filter is a waste of time, but
in general the planner can't be expected to know that.

			regards, tom lane

Thu, 28 Aug 2003 19:04:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New array functions 
Joe Conway wrote:
> Tom Lane wrote:
>> Did we discuss this already?  I'd forgotten.
>>
> I can't find it in the archives for some reason, but here was the exchange:
> 
> Tom Lane wrote:
>  > Bruce Momjian <pgman@candle.pha.pa.us> writes:
>  >
>  >>Joe Conway wrote:
>  >>
>  >>>I do agree that it makes contrib/array unnecessary. I was going to
>  >>>suggest we remove that if this was committed.
>  >
>  >>Good idea.
>  >
>  > We could do that, but it might be more friendly to just mark it as
>  > deprecated for one release cycle before zapping it.  That'd give
>  > people who use it some time to convert over.
> 
> So I guess since it was actually you who objected, you have the right to 
> change your mind ;-)
> 

Here is a patch that removes contrib/array, leaving only the README with 
some examples of the new syntax and a reference to the documentation.

I'll try to take a look at contrib/intarray and contrib/intagg before 
the weekend is through, and at least post a recommendation.

Joe
Sat, 30 Aug 2003 21:42:12 -0700
Joe Conway <mail@joeconway.com>
Re: [HACKERS] New array functions

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Did we discuss this already?  I'd forgotten.
> >>
> > I can't find it in the archives for some reason, but here was the exchange:
> > 
> > Tom Lane wrote:
> >  > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >  >
> >  >>Joe Conway wrote:
> >  >>
> >  >>>I do agree that it makes contrib/array unnecessary. I was going to
> >  >>>suggest we remove that if this was committed.
> >  >
> >  >>Good idea.
> >  >
> >  > We could do that, but it might be more friendly to just mark it as
> >  > deprecated for one release cycle before zapping it.  That'd give
> >  > people who use it some time to convert over.
> > 
> > So I guess since it was actually you who objected, you have the right to 
> > change your mind ;-)
> > 
> 
> Here is a patch that removes contrib/array, leaving only the README with 
> some examples of the new syntax and a reference to the documentation.
> 
> I'll try to take a look at contrib/intarray and contrib/intagg before 
> the weekend is through, and at least post a recommendation.
> 
> Joe
> 

> Index: contrib/array/Makefile
> ===================================================================
> RCS file: contrib/array/Makefile
> diff -N contrib/array/Makefile
> *** contrib/array/Makefile	6 Sep 2001 10:49:29 -0000	1.16
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,11 ****
> - # $Header: /opt/src/cvs/pgsql-server/contrib/array/Makefile,v 1.16 2001/09/06 10:49:29 petere Exp $
> - 
> - subdir = contrib/array
> - top_builddir = ../..
> - include $(top_builddir)/src/Makefile.global
> - 
> - MODULES = array_iterator
> - DATA_built = array_iterator.sql
> - DOCS = README.array_iterator
> - 
> - include $(top_srcdir)/contrib/contrib-global.mk
> --- 0 ----
> Index: contrib/array/README.array_iterator
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/array/README.array_iterator,v
> retrieving revision 1.2
> diff -c -r1.2 README.array_iterator
> *** contrib/array/README.array_iterator	26 Aug 2002 17:53:57 -0000	1.2
> --- contrib/array/README.array_iterator	31 Aug 2003 04:37:04 -0000
> ***************
> *** 1,49 ****
> ! Array iterator functions, by Massimo Dal Zotto <dz@cs.unitn.it>
> ! Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>
>   
> ! This software is distributed under the GNU General Public License
> ! either version 2, or (at your option) any later version.
>   
>   
> ! 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 operator requires writing a different C function.
> ! This is tedious, and error-prone since one must take care that the correct
> ! datatypes are associated with the selected underlying function.
> ! 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.
> --- 1,32 ----
> ! Array iterator functions have been removed as of PostgreSQL 7.4, because
> ! equivalent functionality is now available built in to the backend.
>   
> ! For example, previously, using contrib/array, you might have used the
> ! following construct:
>   
> +     create table t(id int4[], txt text[]);
>   
> !     -- select tuples with some id element equal to 123
> !     select * from t where t.id *= 123;
> ! 
> ! Now you would do this instead:
> ! 
> !     -- select tuples with some id element equal to 123
> !     select * from t where 123 = any (t.id);
> ! 
> !     -- or you could also do this
> !     select * from t where 123 = some (t.id);
> ! 
> ! Similarly, if using contrib/array, you did the following:
> ! 
> !     -- select tuples with all txt elements matching '^[A-Z]'
> !     select * from t where t.txt[1:3] **~ '^[A-Z]';
> ! 
> ! Now do this instead:
> ! 
> !     -- select tuples with all txt elements matching '^[A-Z]'
> !     select * from t where '^[A-Z]' ~ all (t.txt[1:3]);
> ! 
> ! See the related section in the online documentation for more detail:
> !     Table of Contents => Functions and Operators => Row and Array Comparisons
>   
> Index: contrib/array/array_iterator.c
> ===================================================================
> RCS file: contrib/array/array_iterator.c
> diff -N contrib/array/array_iterator.c
> *** contrib/array/array_iterator.c	26 May 2003 00:11:27 -0000	1.26
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,333 ****
> - /*
> -  * array_iterator.c --
> -  *
> -  * This file defines a new class of operators 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.
> -  *
> -  * Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>
> -  * ported to postgreSQL 6.3.2,added oid_functions, 18.1.1999,
> -  * Tobias Gabele <gabele@wiz.uni-kassel.de>
> -  *
> -  * This software is distributed under the GNU General Public License
> -  * either version 2, or (at your option) any later version.
> -  */
> - 
> - #include "postgres.h"
> - 
> - #include <ctype.h>
> - #include <stdio.h>
> - #include <sys/types.h>
> - #include <string.h>
> - 
> - #include "access/tupmacs.h"
> - #include "access/xact.h"
> - #include "fmgr.h"
> - #include "miscadmin.h"
> - #include "utils/array.h"
> - #include "utils/builtins.h"
> - #include "utils/fmgroids.h"
> - #include "utils/memutils.h"
> - #include "utils/lsyscache.h"
> - 
> - #include "array_iterator.h"
> - 
> - 
> - static int32
> - array_iterator(Oid proc, int and, ArrayType *array, Datum value)
> - {
> - 	Oid			elemtype;
> - 	int16		typlen;
> - 	bool		typbyval;
> - 	char		typalign;
> - 	int			nitems,
> - 				i;
> - 	Datum		result;
> - 	int			ndim,
> - 			   *dim;
> - 	char	   *p;
> - 	FmgrInfo	finfo;
> - 
> - 	/* Sanity checks */
> - 	if (array == (ArrayType *) NULL)
> - 	{
> - 		/* elog(WARNING, "array_iterator: array is null"); */
> - 		return (0);
> - 	}
> - 
> - 	/* detoast input if necessary */
> - 	array = DatumGetArrayTypeP(PointerGetDatum(array));
> - 
> - 	ndim = ARR_NDIM(array);
> - 	dim = ARR_DIMS(array);
> - 	nitems = ArrayGetNItems(ndim, dim);
> - 	if (nitems == 0)
> - 		return (0);
> - 
> - 	/* Lookup element type information */
> - 	elemtype = ARR_ELEMTYPE(array);
> - 	get_typlenbyvalalign(elemtype, &typlen, &typbyval, &typalign);
> - 
> - 	/* Lookup the function entry point */
> - 	fmgr_info(proc, &finfo);
> - 	if (finfo.fn_nargs != 2)
> - 	{
> - 		elog(ERROR, "array_iterator: proc %u does not take 2 args", proc);
> - 		return (0);
> - 	}
> - 
> - 	/* Scan the array and apply the operator to each element */
> - 	result = BoolGetDatum(false);
> - 	p = ARR_DATA_PTR(array);
> - 	for (i = 0; i < nitems; i++)
> - 	{
> - 		Datum		itemvalue;
> - 
> - 		itemvalue = fetch_att(p, typbyval, typlen);
> - 
> - 		p = att_addlength(p, typlen, PointerGetDatum(p));
> - 		p = (char *) att_align(p, typalign);
> - 
> - 		result = FunctionCall2(&finfo, itemvalue, value);
> - 
> - 		if (DatumGetBool(result))
> - 		{
> - 			if (!and)
> - 				return (1);
> - 		}
> - 		else
> - 		{
> - 			if (and)
> - 				return (0);
> - 		}
> - 	}
> - 
> - 	if (and && DatumGetBool(result))
> - 		return (1);
> - 	else
> - 		return (0);
> - }
> - 
> - /*
> -  * Iterator functions for type _text
> -  */
> - 
> - int32
> - array_texteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_texteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTEQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_textregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_textregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - /*
> -  * Iterator functions for type _bpchar. Note that the regexp
> -  * operators take the second argument of type text.
> -  */
> - 
> - int32
> - array_bpchareq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_BPCHAREQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_bpchareq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_BPCHAREQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_bpcharregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_bpcharregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - /*
> -  * Iterator functions for type _int4
> -  */
> - 
> - int32
> - array_int4eq(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4EQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4eq(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4EQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4ne(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4NE,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4ne(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4NE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4gt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GT,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4gt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GT,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4ge(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GE,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4ge(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4lt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LT,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4lt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LT,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4le(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LE,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4le(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - /* new tobias gabele 1999 */
> - 
> - int32
> - array_oideq(ArrayType *array, Oid value)
> - {
> - 	return array_iterator(F_OIDEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_oidne(ArrayType *array, Oid value)
> - {
> - 	return array_iterator(F_OIDNE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_ineteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_EQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_ineteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_EQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_inetne(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_NE,
> - 						  0,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_inetne(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_NE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> --- 0 ----
> Index: contrib/array/array_iterator.h
> ===================================================================
> RCS file: contrib/array/array_iterator.h
> diff -N contrib/array/array_iterator.h
> *** contrib/array/array_iterator.h	26 May 2003 00:11:27 -0000	1.10
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,38 ****
> - #ifndef ARRAY_ITERATOR_H
> - #define ARRAY_ITERATOR_H
> - 
> - static int32 array_iterator(Oid proc, int and,
> - 			   ArrayType *array, Datum value);
> - 
> - int32		array_texteq(ArrayType *array, void *value);
> - int32		array_all_texteq(ArrayType *array, void *value);
> - int32		array_textregexeq(ArrayType *array, void *value);
> - int32		array_all_textregexeq(ArrayType *array, void *value);
> - 
> - int32		array_bpchareq(ArrayType *array, void *value);
> - int32		array_all_bpchareq(ArrayType *array, void *value);
> - int32		array_bpcharregexeq(ArrayType *array, void *value);
> - int32		array_all_bpcharregexeq(ArrayType *array, void *value);
> - 
> - int32		array_int4eq(ArrayType *array, int4 value);
> - int32		array_all_int4eq(ArrayType *array, int4 value);
> - int32		array_int4ne(ArrayType *array, int4 value);
> - int32		array_all_int4ne(ArrayType *array, int4 value);
> - int32		array_int4gt(ArrayType *array, int4 value);
> - int32		array_all_int4gt(ArrayType *array, int4 value);
> - int32		array_int4ge(ArrayType *array, int4 value);
> - int32		array_all_int4ge(ArrayType *array, int4 value);
> - int32		array_int4lt(ArrayType *array, int4 value);
> - int32		array_all_int4lt(ArrayType *array, int4 value);
> - int32		array_int4le(ArrayType *array, int4 value);
> - int32		array_all_int4le(ArrayType *array, int4 value);
> - 
> - int32		array_oideq(ArrayType *array, Oid value);
> - int32		array_all_oidne(ArrayType *array, Oid value);
> - 
> - int32		array_ineteq(ArrayType *array, void *value);
> - int32		array_all_ineteq(ArrayType *array, void *value);
> - int32		array_inetne(ArrayType *array, void *value);
> - int32		array_all_inetne(ArrayType *array, void *value);
> - 
> - #endif
> --- 0 ----
> Index: contrib/array/array_iterator.sql.in
> ===================================================================
> RCS file: contrib/array/array_iterator.sql.in
> diff -N contrib/array/array_iterator.sql.in
> *** contrib/array/array_iterator.sql.in	26 May 2003 00:11:27 -0000	1.10
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,329 ****
> - -- SQL code to define the new array iterator functions and operators
> - 
> - -- define the array operators *=, **=, *~ and **~ for type _text
> - --
> - 
> - -- Adjust this setting to control where the objects get created.
> - SET search_path = public;
> - 
> - CREATE OR REPLACE FUNCTION array_texteq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_texteq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_textregexeq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_textregexeq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_text,text);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_text, 
> - 	RIGHTARG=text, 
> - 	PROCEDURE=array_texteq
> - );
> - 
> - DROP OPERATOR **=(_text,text);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_text,
> - 	RIGHTARG=text,
> - 	PROCEDURE=array_all_texteq
> - );
> - 
> - DROP OPERATOR *~(_text,text);
> - CREATE OPERATOR *~ (
> - 	LEFTARG=_text,
> - 	RIGHTARG=text,
> - 	PROCEDURE=array_textregexeq
> - );
> - 
> - DROP OPERATOR **~(_text,text);
> - CREATE OPERATOR **~ (
> - 	LEFTARG=_text,
> - 	RIGHTARG=text,
> - 	PROCEDURE=array_all_textregexeq
> - );
> - 
> - 
> - -- define the array operators *=, **=, *~ and **~ for type _bpchar
> - --
> - CREATE OR REPLACE FUNCTION array_bpchareq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_bpchareq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_bpcharregexeq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_bpcharregexeq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_bpchar,bpchar);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_bpchar, 
> - 	RIGHTARG=bpchar, 
> - 	PROCEDURE=array_bpchareq
> - );
> - 
> - DROP OPERATOR **=(_bpchar,bpchar);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_bpchar,
> - 	RIGHTARG=bpchar,
> - 	PROCEDURE=array_all_bpchareq
> - );
> - 
> - DROP OPERATOR *~(_bpchar,bpchar);
> - CREATE OPERATOR *~ (
> - 	LEFTARG=_bpchar,
> - 	RIGHTARG=bpchar,
> - 	PROCEDURE=array_bpcharregexeq
> - );
> - 
> - DROP OPERATOR **~(_bpchar,bpchar);
> - CREATE OPERATOR **~ (
> - 	LEFTARG=_bpchar,
> - 	RIGHTARG=bpchar,
> - 	PROCEDURE=array_all_bpcharregexeq
> - );
> - 
> - 
> - -- define the array operators *=, **=, *> and **> for type _int4
> - --
> - CREATE OR REPLACE FUNCTION array_int4eq(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4eq(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4ne(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4ne(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4gt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4gt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4ge(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4ge(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4lt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4lt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4le(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4le(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_int4,int4);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4eq
> - );
> - 
> - DROP OPERATOR **=(_int4,int4);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4eq
> - );
> - 
> - DROP OPERATOR *<>(_int4,int4);
> - CREATE OPERATOR *<> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4ne
> - );
> - 
> - DROP OPERATOR **<>(_int4,int4);
> - CREATE OPERATOR **<> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4ne
> - );
> - 
> - DROP OPERATOR *>(_int4,int4);
> - CREATE OPERATOR *> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4gt
> - );
> - 
> - DROP OPERATOR **>(_int4,int4);
> - CREATE OPERATOR **> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4gt
> - );
> - 
> - DROP OPERATOR *>=(_int4,int4);
> - CREATE OPERATOR *>= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4ge
> - );
> - 
> - DROP OPERATOR **>=(_int4,int4);
> - CREATE OPERATOR **>= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4ge
> - );
> - 
> - DROP OPERATOR *<(_int4,int4);
> - CREATE OPERATOR *< (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4lt
> - );
> - 
> - DROP OPERATOR **<(_int4,int4);
> - CREATE OPERATOR **< (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4lt
> - );
> - 
> - DROP OPERATOR *<=(_int4,int4);
> - CREATE OPERATOR *<= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4le
> - );
> - 
> - DROP OPERATOR **<=(_int4,int4);
> - CREATE OPERATOR **<= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4le
> - );
> - 
> - -- define the array operators *=, **<>  for type _oid  (added tobias 1. 1999)
> - --
> - CREATE OR REPLACE FUNCTION array_oideq(_oid, oid)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_oidne(_oid, oid)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_oid,oid);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_oid, 
> - 	RIGHTARG=oid, 
> - 	PROCEDURE=array_oideq
> - );
> - 
> - DROP OPERATOR **<>(_oid,oid);
> - CREATE OPERATOR **<> (
> - 	LEFTARG=_oid,
> - 	RIGHTARG=oid,
> - 	PROCEDURE=array_all_oidne
> - );
> - 
> - -- define the array operators *=, **=, *<>, **<> for type _inet
> - 
> - CREATE OR REPLACE FUNCTION array_ineteq(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_ineteq(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_inetne(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_inetne(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_inet,inet);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_ineteq
> - );
> - 
> - DROP OPERATOR **=(_inet,inet);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_all_ineteq
> - );
> - 
> - DROP OPERATOR *<>(_inet,inet);
> - CREATE OPERATOR *<> (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_inetne
> - );
> - 
> - DROP OPERATOR **<>(_inet,inet);
> - CREATE OPERATOR **<> (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_all_inetne
> - );
> --- 0 ----
> Index: contrib/Makefile
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/Makefile,v
> retrieving revision 1.45
> diff -c -r1.45 Makefile
> *** contrib/Makefile	24 Jul 2003 16:54:58 -0000	1.45
> --- contrib/Makefile	31 Aug 2003 04:47:01 -0000
> ***************
> *** 5,11 ****
>   include $(top_builddir)/src/Makefile.global
>   
>   WANTED_DIRS = \
> - 		array		\
>   		btree_gist	\
>   		chkpass		\
>   		cube		\
> --- 5,10 ----
> ***************
> *** 44,49 ****
> --- 43,49 ----
>   		vacuumlo
>   
>   # Missing:
> + #		array		\ (removed all but the README)
>   #		adddepend	\ (does not have a makefile)
>   #		ipc_check	\ (does not have a makefile)
>   #		mSQL-interface	\ (requires msql installed)

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 10 Sep 2003 00:14:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] New array functions

Patch applied.  Thanks.

---------------------------------------------------------------------------


Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Did we discuss this already?  I'd forgotten.
> >>
> > I can't find it in the archives for some reason, but here was the exchange:
> > 
> > Tom Lane wrote:
> >  > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >  >
> >  >>Joe Conway wrote:
> >  >>
> >  >>>I do agree that it makes contrib/array unnecessary. I was going to
> >  >>>suggest we remove that if this was committed.
> >  >
> >  >>Good idea.
> >  >
> >  > We could do that, but it might be more friendly to just mark it as
> >  > deprecated for one release cycle before zapping it.  That'd give
> >  > people who use it some time to convert over.
> > 
> > So I guess since it was actually you who objected, you have the right to 
> > change your mind ;-)
> > 
> 
> Here is a patch that removes contrib/array, leaving only the README with 
> some examples of the new syntax and a reference to the documentation.
> 
> I'll try to take a look at contrib/intarray and contrib/intagg before 
> the weekend is through, and at least post a recommendation.
> 
> Joe
> 

> Index: contrib/array/Makefile
> ===================================================================
> RCS file: contrib/array/Makefile
> diff -N contrib/array/Makefile
> *** contrib/array/Makefile	6 Sep 2001 10:49:29 -0000	1.16
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,11 ****
> - # $Header: /opt/src/cvs/pgsql-server/contrib/array/Makefile,v 1.16 2001/09/06 10:49:29 petere Exp $
> - 
> - subdir = contrib/array
> - top_builddir = ../..
> - include $(top_builddir)/src/Makefile.global
> - 
> - MODULES = array_iterator
> - DATA_built = array_iterator.sql
> - DOCS = README.array_iterator
> - 
> - include $(top_srcdir)/contrib/contrib-global.mk
> --- 0 ----
> Index: contrib/array/README.array_iterator
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/array/README.array_iterator,v
> retrieving revision 1.2
> diff -c -r1.2 README.array_iterator
> *** contrib/array/README.array_iterator	26 Aug 2002 17:53:57 -0000	1.2
> --- contrib/array/README.array_iterator	31 Aug 2003 04:37:04 -0000
> ***************
> *** 1,49 ****
> ! Array iterator functions, by Massimo Dal Zotto <dz@cs.unitn.it>
> ! Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>
>   
> ! This software is distributed under the GNU General Public License
> ! either version 2, or (at your option) any later version.
>   
>   
> ! 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 operator requires writing a different C function.
> ! This is tedious, and error-prone since one must take care that the correct
> ! datatypes are associated with the selected underlying function.
> ! 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.
> --- 1,32 ----
> ! Array iterator functions have been removed as of PostgreSQL 7.4, because
> ! equivalent functionality is now available built in to the backend.
>   
> ! For example, previously, using contrib/array, you might have used the
> ! following construct:
>   
> +     create table t(id int4[], txt text[]);
>   
> !     -- select tuples with some id element equal to 123
> !     select * from t where t.id *= 123;
> ! 
> ! Now you would do this instead:
> ! 
> !     -- select tuples with some id element equal to 123
> !     select * from t where 123 = any (t.id);
> ! 
> !     -- or you could also do this
> !     select * from t where 123 = some (t.id);
> ! 
> ! Similarly, if using contrib/array, you did the following:
> ! 
> !     -- select tuples with all txt elements matching '^[A-Z]'
> !     select * from t where t.txt[1:3] **~ '^[A-Z]';
> ! 
> ! Now do this instead:
> ! 
> !     -- select tuples with all txt elements matching '^[A-Z]'
> !     select * from t where '^[A-Z]' ~ all (t.txt[1:3]);
> ! 
> ! See the related section in the online documentation for more detail:
> !     Table of Contents => Functions and Operators => Row and Array Comparisons
>   
> Index: contrib/array/array_iterator.c
> ===================================================================
> RCS file: contrib/array/array_iterator.c
> diff -N contrib/array/array_iterator.c
> *** contrib/array/array_iterator.c	26 May 2003 00:11:27 -0000	1.26
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,333 ****
> - /*
> -  * array_iterator.c --
> -  *
> -  * This file defines a new class of operators 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.
> -  *
> -  * Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>
> -  * ported to postgreSQL 6.3.2,added oid_functions, 18.1.1999,
> -  * Tobias Gabele <gabele@wiz.uni-kassel.de>
> -  *
> -  * This software is distributed under the GNU General Public License
> -  * either version 2, or (at your option) any later version.
> -  */
> - 
> - #include "postgres.h"
> - 
> - #include <ctype.h>
> - #include <stdio.h>
> - #include <sys/types.h>
> - #include <string.h>
> - 
> - #include "access/tupmacs.h"
> - #include "access/xact.h"
> - #include "fmgr.h"
> - #include "miscadmin.h"
> - #include "utils/array.h"
> - #include "utils/builtins.h"
> - #include "utils/fmgroids.h"
> - #include "utils/memutils.h"
> - #include "utils/lsyscache.h"
> - 
> - #include "array_iterator.h"
> - 
> - 
> - static int32
> - array_iterator(Oid proc, int and, ArrayType *array, Datum value)
> - {
> - 	Oid			elemtype;
> - 	int16		typlen;
> - 	bool		typbyval;
> - 	char		typalign;
> - 	int			nitems,
> - 				i;
> - 	Datum		result;
> - 	int			ndim,
> - 			   *dim;
> - 	char	   *p;
> - 	FmgrInfo	finfo;
> - 
> - 	/* Sanity checks */
> - 	if (array == (ArrayType *) NULL)
> - 	{
> - 		/* elog(WARNING, "array_iterator: array is null"); */
> - 		return (0);
> - 	}
> - 
> - 	/* detoast input if necessary */
> - 	array = DatumGetArrayTypeP(PointerGetDatum(array));
> - 
> - 	ndim = ARR_NDIM(array);
> - 	dim = ARR_DIMS(array);
> - 	nitems = ArrayGetNItems(ndim, dim);
> - 	if (nitems == 0)
> - 		return (0);
> - 
> - 	/* Lookup element type information */
> - 	elemtype = ARR_ELEMTYPE(array);
> - 	get_typlenbyvalalign(elemtype, &typlen, &typbyval, &typalign);
> - 
> - 	/* Lookup the function entry point */
> - 	fmgr_info(proc, &finfo);
> - 	if (finfo.fn_nargs != 2)
> - 	{
> - 		elog(ERROR, "array_iterator: proc %u does not take 2 args", proc);
> - 		return (0);
> - 	}
> - 
> - 	/* Scan the array and apply the operator to each element */
> - 	result = BoolGetDatum(false);
> - 	p = ARR_DATA_PTR(array);
> - 	for (i = 0; i < nitems; i++)
> - 	{
> - 		Datum		itemvalue;
> - 
> - 		itemvalue = fetch_att(p, typbyval, typlen);
> - 
> - 		p = att_addlength(p, typlen, PointerGetDatum(p));
> - 		p = (char *) att_align(p, typalign);
> - 
> - 		result = FunctionCall2(&finfo, itemvalue, value);
> - 
> - 		if (DatumGetBool(result))
> - 		{
> - 			if (!and)
> - 				return (1);
> - 		}
> - 		else
> - 		{
> - 			if (and)
> - 				return (0);
> - 		}
> - 	}
> - 
> - 	if (and && DatumGetBool(result))
> - 		return (1);
> - 	else
> - 		return (0);
> - }
> - 
> - /*
> -  * Iterator functions for type _text
> -  */
> - 
> - int32
> - array_texteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_texteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTEQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_textregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_textregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - /*
> -  * Iterator functions for type _bpchar. Note that the regexp
> -  * operators take the second argument of type text.
> -  */
> - 
> - int32
> - array_bpchareq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_BPCHAREQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_bpchareq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_BPCHAREQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_bpcharregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_bpcharregexeq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_TEXTREGEXEQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - /*
> -  * Iterator functions for type _int4
> -  */
> - 
> - int32
> - array_int4eq(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4EQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4eq(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4EQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4ne(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4NE,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4ne(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4NE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4gt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GT,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4gt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GT,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4ge(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GE,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4ge(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4GE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4lt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LT,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4lt(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LT,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_int4le(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LE,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_int4le(ArrayType *array, int4 value)
> - {
> - 	return array_iterator(F_INT4LE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - /* new tobias gabele 1999 */
> - 
> - int32
> - array_oideq(ArrayType *array, Oid value)
> - {
> - 	return array_iterator(F_OIDEQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_oidne(ArrayType *array, Oid value)
> - {
> - 	return array_iterator(F_OIDNE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_ineteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_EQ,
> - 						  0,	/* logical or */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_ineteq(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_EQ,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_inetne(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_NE,
> - 						  0,	/* logical and */
> - 						  array, (Datum) value);
> - }
> - 
> - int32
> - array_all_inetne(ArrayType *array, void *value)
> - {
> - 	return array_iterator(F_NETWORK_NE,
> - 						  1,	/* logical and */
> - 						  array, (Datum) value);
> - }
> --- 0 ----
> Index: contrib/array/array_iterator.h
> ===================================================================
> RCS file: contrib/array/array_iterator.h
> diff -N contrib/array/array_iterator.h
> *** contrib/array/array_iterator.h	26 May 2003 00:11:27 -0000	1.10
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,38 ****
> - #ifndef ARRAY_ITERATOR_H
> - #define ARRAY_ITERATOR_H
> - 
> - static int32 array_iterator(Oid proc, int and,
> - 			   ArrayType *array, Datum value);
> - 
> - int32		array_texteq(ArrayType *array, void *value);
> - int32		array_all_texteq(ArrayType *array, void *value);
> - int32		array_textregexeq(ArrayType *array, void *value);
> - int32		array_all_textregexeq(ArrayType *array, void *value);
> - 
> - int32		array_bpchareq(ArrayType *array, void *value);
> - int32		array_all_bpchareq(ArrayType *array, void *value);
> - int32		array_bpcharregexeq(ArrayType *array, void *value);
> - int32		array_all_bpcharregexeq(ArrayType *array, void *value);
> - 
> - int32		array_int4eq(ArrayType *array, int4 value);
> - int32		array_all_int4eq(ArrayType *array, int4 value);
> - int32		array_int4ne(ArrayType *array, int4 value);
> - int32		array_all_int4ne(ArrayType *array, int4 value);
> - int32		array_int4gt(ArrayType *array, int4 value);
> - int32		array_all_int4gt(ArrayType *array, int4 value);
> - int32		array_int4ge(ArrayType *array, int4 value);
> - int32		array_all_int4ge(ArrayType *array, int4 value);
> - int32		array_int4lt(ArrayType *array, int4 value);
> - int32		array_all_int4lt(ArrayType *array, int4 value);
> - int32		array_int4le(ArrayType *array, int4 value);
> - int32		array_all_int4le(ArrayType *array, int4 value);
> - 
> - int32		array_oideq(ArrayType *array, Oid value);
> - int32		array_all_oidne(ArrayType *array, Oid value);
> - 
> - int32		array_ineteq(ArrayType *array, void *value);
> - int32		array_all_ineteq(ArrayType *array, void *value);
> - int32		array_inetne(ArrayType *array, void *value);
> - int32		array_all_inetne(ArrayType *array, void *value);
> - 
> - #endif
> --- 0 ----
> Index: contrib/array/array_iterator.sql.in
> ===================================================================
> RCS file: contrib/array/array_iterator.sql.in
> diff -N contrib/array/array_iterator.sql.in
> *** contrib/array/array_iterator.sql.in	26 May 2003 00:11:27 -0000	1.10
> --- /dev/null	1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,329 ****
> - -- SQL code to define the new array iterator functions and operators
> - 
> - -- define the array operators *=, **=, *~ and **~ for type _text
> - --
> - 
> - -- Adjust this setting to control where the objects get created.
> - SET search_path = public;
> - 
> - CREATE OR REPLACE FUNCTION array_texteq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_texteq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_textregexeq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_textregexeq(_text, text)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_text,text);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_text, 
> - 	RIGHTARG=text, 
> - 	PROCEDURE=array_texteq
> - );
> - 
> - DROP OPERATOR **=(_text,text);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_text,
> - 	RIGHTARG=text,
> - 	PROCEDURE=array_all_texteq
> - );
> - 
> - DROP OPERATOR *~(_text,text);
> - CREATE OPERATOR *~ (
> - 	LEFTARG=_text,
> - 	RIGHTARG=text,
> - 	PROCEDURE=array_textregexeq
> - );
> - 
> - DROP OPERATOR **~(_text,text);
> - CREATE OPERATOR **~ (
> - 	LEFTARG=_text,
> - 	RIGHTARG=text,
> - 	PROCEDURE=array_all_textregexeq
> - );
> - 
> - 
> - -- define the array operators *=, **=, *~ and **~ for type _bpchar
> - --
> - CREATE OR REPLACE FUNCTION array_bpchareq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_bpchareq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_bpcharregexeq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_bpcharregexeq(_bpchar, bpchar)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_bpchar,bpchar);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_bpchar, 
> - 	RIGHTARG=bpchar, 
> - 	PROCEDURE=array_bpchareq
> - );
> - 
> - DROP OPERATOR **=(_bpchar,bpchar);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_bpchar,
> - 	RIGHTARG=bpchar,
> - 	PROCEDURE=array_all_bpchareq
> - );
> - 
> - DROP OPERATOR *~(_bpchar,bpchar);
> - CREATE OPERATOR *~ (
> - 	LEFTARG=_bpchar,
> - 	RIGHTARG=bpchar,
> - 	PROCEDURE=array_bpcharregexeq
> - );
> - 
> - DROP OPERATOR **~(_bpchar,bpchar);
> - CREATE OPERATOR **~ (
> - 	LEFTARG=_bpchar,
> - 	RIGHTARG=bpchar,
> - 	PROCEDURE=array_all_bpcharregexeq
> - );
> - 
> - 
> - -- define the array operators *=, **=, *> and **> for type _int4
> - --
> - CREATE OR REPLACE FUNCTION array_int4eq(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4eq(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4ne(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4ne(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4gt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4gt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4ge(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4ge(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4lt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4lt(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_int4le(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_int4le(_int4, int4)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_int4,int4);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4eq
> - );
> - 
> - DROP OPERATOR **=(_int4,int4);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4eq
> - );
> - 
> - DROP OPERATOR *<>(_int4,int4);
> - CREATE OPERATOR *<> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4ne
> - );
> - 
> - DROP OPERATOR **<>(_int4,int4);
> - CREATE OPERATOR **<> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4ne
> - );
> - 
> - DROP OPERATOR *>(_int4,int4);
> - CREATE OPERATOR *> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4gt
> - );
> - 
> - DROP OPERATOR **>(_int4,int4);
> - CREATE OPERATOR **> (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4gt
> - );
> - 
> - DROP OPERATOR *>=(_int4,int4);
> - CREATE OPERATOR *>= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4ge
> - );
> - 
> - DROP OPERATOR **>=(_int4,int4);
> - CREATE OPERATOR **>= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4ge
> - );
> - 
> - DROP OPERATOR *<(_int4,int4);
> - CREATE OPERATOR *< (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4lt
> - );
> - 
> - DROP OPERATOR **<(_int4,int4);
> - CREATE OPERATOR **< (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4lt
> - );
> - 
> - DROP OPERATOR *<=(_int4,int4);
> - CREATE OPERATOR *<= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_int4le
> - );
> - 
> - DROP OPERATOR **<=(_int4,int4);
> - CREATE OPERATOR **<= (
> - 	LEFTARG=_int4,
> - 	RIGHTARG=int4,
> - 	PROCEDURE=array_all_int4le
> - );
> - 
> - -- define the array operators *=, **<>  for type _oid  (added tobias 1. 1999)
> - --
> - CREATE OR REPLACE FUNCTION array_oideq(_oid, oid)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_oidne(_oid, oid)
> - RETURNS bool
> - AS 'MODULE_PATHNAME' 
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_oid,oid);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_oid, 
> - 	RIGHTARG=oid, 
> - 	PROCEDURE=array_oideq
> - );
> - 
> - DROP OPERATOR **<>(_oid,oid);
> - CREATE OPERATOR **<> (
> - 	LEFTARG=_oid,
> - 	RIGHTARG=oid,
> - 	PROCEDURE=array_all_oidne
> - );
> - 
> - -- define the array operators *=, **=, *<>, **<> for type _inet
> - 
> - CREATE OR REPLACE FUNCTION array_ineteq(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_ineteq(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_inetne(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - CREATE OR REPLACE FUNCTION array_all_inetne(_inet, inet)
> - RETURNS bool
> - AS 'MODULE_PATHNAME'
> - LANGUAGE 'C' IMMUTABLE STRICT;
> - 
> - DROP OPERATOR *=(_inet,inet);
> - CREATE OPERATOR *= (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_ineteq
> - );
> - 
> - DROP OPERATOR **=(_inet,inet);
> - CREATE OPERATOR **= (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_all_ineteq
> - );
> - 
> - DROP OPERATOR *<>(_inet,inet);
> - CREATE OPERATOR *<> (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_inetne
> - );
> - 
> - DROP OPERATOR **<>(_inet,inet);
> - CREATE OPERATOR **<> (
> - 	LEFTARG=_inet,
> - 	RIGHTARG=inet,
> - 	PROCEDURE=array_all_inetne
> - );
> --- 0 ----
> Index: contrib/Makefile
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/Makefile,v
> retrieving revision 1.45
> diff -c -r1.45 Makefile
> *** contrib/Makefile	24 Jul 2003 16:54:58 -0000	1.45
> --- contrib/Makefile	31 Aug 2003 04:47:01 -0000
> ***************
> *** 5,11 ****
>   include $(top_builddir)/src/Makefile.global
>   
>   WANTED_DIRS = \
> - 		array		\
>   		btree_gist	\
>   		chkpass		\
>   		cube		\
> --- 5,10 ----
> ***************
> *** 44,49 ****
> --- 43,49 ----
>   		vacuumlo
>   
>   # Missing:
> + #		array		\ (removed all but the README)
>   #		adddepend	\ (does not have a makefile)
>   #		ipc_check	\ (does not have a makefile)
>   #		mSQL-interface	\ (requires msql installed)

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 11 Sep 2003 13:15:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] New array functions
Greg Stark <gsstark@mit.edu> writes:
> Joe Conway wrote:
>> I'll try to take a look at contrib/intarray and contrib/intagg before 
>> the weekend is through, and at least post a recommendation.

> Can I pipe up in int_aggregate.c's defense ?

I don't think we're removing it just yet.  The contrib/array stuff
seemed completely obsolete, but intagg isn't.

			regards, tom lane

Sun, 14 Sep 2003 22:52:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] New array functions 