one last patch - array lower and upper bound - Mailing list pgsql-patches

The "Allow easy display of usernames in a group (pg_hba.conf uses groups
now)" item on the open items, and subsequent plpgsql function I sent in,
made me realize it was too hard to get the upper and lower bound of an
array. The attached creates two functions that I think will be very
useful when combined with the ability of plpgsql to return sets.

array_lower(array, dim_num)
- and -
array_upper(array, dim_num)

They return the value (as an int) of the upper and lower bound of the
requested dim in the provided array.

With these, the show_group() function from before looks like:

CREATE OR REPLACE FUNCTION show_group(text) RETURNS SETOF text AS '
DECLARE
   loginname text;
   low int;
   high int;
BEGIN
   SELECT INTO low array_lower(grolist,1)
     FROM pg_group WHERE groname = $1;
   SELECT INTO high array_upper(grolist,1)
     FROM pg_group WHERE groname = $1;

   FOR i IN low..high LOOP
     SELECT INTO loginname s.usename
       FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i];
     RETURN NEXT loginname;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql';

If possible, and no objections, please apply for 7.3.

catversion.h bump and initdb required.

Thanks,

Joe
Index: src/backend/utils/adt/arrayfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/arrayfuncs.c,v
retrieving revision 1.79
diff -c -r1.79 arrayfuncs.c
*** src/backend/utils/adt/arrayfuncs.c    26 Aug 2002 17:53:58 -0000    1.79
--- src/backend/utils/adt/arrayfuncs.c    2 Sep 2002 06:25:31 -0000
***************
*** 799,804 ****
--- 799,863 ----
      PG_RETURN_TEXT_P(result);
  }

+ /*-----------------------------------------------------------------------------
+  * array_lower :
+  *        returns the lower dimension, of the DIM requested, for
+  *        the array pointed to by "v", as an int4
+  *----------------------------------------------------------------------------
+  */
+ Datum
+ array_lower(PG_FUNCTION_ARGS)
+ {
+     ArrayType  *v = PG_GETARG_ARRAYTYPE_P(0);
+     int            reqdim = PG_GETARG_INT32(1);
+     int           *lb;
+     int            result;
+
+     /* Sanity check: does it look like an array at all? */
+     if (ARR_NDIM(v) <= 0 || ARR_NDIM(v) > MAXDIM)
+         PG_RETURN_NULL();
+
+     /* Sanity check: was the requested dim valid */
+     if (reqdim <= 0 || reqdim > ARR_NDIM(v))
+         PG_RETURN_NULL();
+
+     lb = ARR_LBOUND(v);
+     result = lb[reqdim - 1];
+
+     PG_RETURN_INT32(result);
+ }
+
+ /*-----------------------------------------------------------------------------
+  * array_upper :
+  *        returns the upper dimension, of the DIM requested, for
+  *        the array pointed to by "v", as an int4
+  *----------------------------------------------------------------------------
+  */
+ Datum
+ array_upper(PG_FUNCTION_ARGS)
+ {
+     ArrayType  *v = PG_GETARG_ARRAYTYPE_P(0);
+     int            reqdim = PG_GETARG_INT32(1);
+     int           *dimv,
+                *lb;
+     int            result;
+
+     /* Sanity check: does it look like an array at all? */
+     if (ARR_NDIM(v) <= 0 || ARR_NDIM(v) > MAXDIM)
+         PG_RETURN_NULL();
+
+     /* Sanity check: was the requested dim valid */
+     if (reqdim <= 0 || reqdim > ARR_NDIM(v))
+         PG_RETURN_NULL();
+
+     lb = ARR_LBOUND(v);
+     dimv = ARR_DIMS(v);
+
+     result = dimv[reqdim - 1] + lb[reqdim - 1] - 1;
+
+     PG_RETURN_INT32(result);
+ }
+
  /*---------------------------------------------------------------------------
   * array_ref :
   *      This routine takes an array pointer and an index array and returns
Index: src/include/utils/array.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/utils/array.h,v
retrieving revision 1.33
diff -c -r1.33 array.h
*** src/include/utils/array.h    26 Aug 2002 17:54:02 -0000    1.33
--- src/include/utils/array.h    2 Sep 2002 06:06:56 -0000
***************
*** 84,89 ****
--- 84,91 ----
  extern Datum array_out(PG_FUNCTION_ARGS);
  extern Datum array_eq(PG_FUNCTION_ARGS);
  extern Datum array_dims(PG_FUNCTION_ARGS);
+ extern Datum array_lower(PG_FUNCTION_ARGS);
+ extern Datum array_upper(PG_FUNCTION_ARGS);

  extern Datum array_ref(ArrayType *array, int nSubscripts, int *indx,
            int arraylen, int elmlen, bool elmbyval, char elmalign,
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.267
diff -c -r1.267 pg_proc.h
*** src/include/catalog/pg_proc.h    1 Sep 2002 00:58:06 -0000    1.267
--- src/include/catalog/pg_proc.h    2 Sep 2002 06:16:43 -0000
***************
*** 989,994 ****
--- 989,998 ----
  DESCR("array");
  DATA(insert OID = 751 (  array_out           PGNSP PGUID 12 f f t f s 1 2275 "2277"  array_out - _null_ ));
  DESCR("array");
+ DATA(insert OID = 2091 (  array_lower       PGNSP PGUID 12 f f t f i 2 23 "2277 23" array_lower - _null_ ));
+ DESCR("array lower dimension");
+ DATA(insert OID = 2092 (  array_upper       PGNSP PGUID 12 f f t f i 2 23 "2277 23" array_upper - _null_ ));
+ DESCR("array upper dimension");

  DATA(insert OID = 760 (  smgrin               PGNSP PGUID 12 f f t f s 1 210 "2275"  smgrin - _null_ ));
  DESCR("storage manager(internal)");