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@gmx.net