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

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

--
  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