Re: one last patch - array lower and upper bound

Isn't that a new feature? And another new feature being solicited? If we
add this then we will have to put out another beta.

Bruce Momjian writes:

>
> Patch applied. Thanks.
>
> initdb required.
>
> Joe, can you submit a patch for your show_group feature? Thanks.
>
> ---------------------------------------------------------------------------
>
>
> 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
>
>

--
Peter Eisentraut peter_e(at)gmx(dot)net