Thread: Re: [HACKERS] 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
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)
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
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
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