Re: one last patch - array lower and upper bound

This has been saved for the 7.4 release:

http://candle.pha.pa.us/cgi-bin/pgpatches2

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

Joe Conway wrote:
> 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)");

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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