one last patch - array lower and upper bound
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
Attachments:
array-util.1.patchtext/plain; name=array-util.1.patchDownload+65-0
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@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
On Fri, 27 Sep 2002, Bruce Momjian wrote:
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.
...
Bruce,
Is this another one of those missed patches?
I saw this come across the Monday before the freeze at the latest. Indeed it
was this that made me stop rushing to get my C version of this in before the
freeze.
BTW, I don't know if anyone did read the code I posted for comments since there
weren't any comments made, but my version produced what I think of as a
normalised version of pg_group with the extra benefit that is was just a
frontend to a general function. If anyone finds it in the archives (bugger, I
think I've deleted the source tree since doing that) I'd still appreciate
comments on it. I posted it more for technical review/learning than for
inclusion in the source tree.
--
Nigel J. Andrews
The patch is saved for 7.4:
http://candle.pha.pa.us/cgi-bin/pgpatches2
The reason it wasn't applied is because it wasn't a fully packaged
feature. It had the array changes needed, but there was no mechanism to
install the show_group() function so I just saved it for later.
---------------------------------------------------------------------------
Nigel J. Andrews wrote:
On Fri, 27 Sep 2002, Bruce Momjian wrote:
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.
...Bruce,
Is this another one of those missed patches?
I saw this come across the Monday before the freeze at the latest. Indeed it
was this that made me stop rushing to get my C version of this in before the
freeze.BTW, I don't know if anyone did read the code I posted for comments since there
weren't any comments made, but my version produced what I think of as a
normalised version of pg_group with the extra benefit that is was just a
frontend to a general function. If anyone finds it in the archives (bugger, I
think I've deleted the source tree since doing that) I'd still appreciate
comments on it. I posted it more for technical review/learning than for
inclusion in the source tree.--
Nigel J. Andrews
--
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 19073Bruce Momjian wrote:
The reason it wasn't applied is because it wasn't a fully packaged
feature. It had the array changes needed, but there was no mechanism to
install the show_group() function so I just saved it for later.
It's probably a good thing anyway. I think for 7.4 we should discuss, design,
and implement a collection of array functions (including these 2) in order to
make it easier to use arrays in PostgreSQL. Some functions should be moved
from contrib into the backend, and some need to be invented, as these two did.
Combined with PL/pgSQL returning sets, it would make arrays a much more
powerful and useful feature than they are today.
Joe
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 19073Bruce Momjian wrote:
Patch applied. Thanks.
initdb required.
Joe, can you submit a patch for your show_group feature? Thanks.
Thanks. As pointed out by Tom, I need to follow up with a doc patch. I will
get that done in the next few days.
show_group was a plpgsql function -- do you want a backend C version, or if
not, where would it go, contrib?
Joe
Joe Conway wrote:
Bruce Momjian wrote:
Patch applied. Thanks.
initdb required.
Joe, can you submit a patch for your show_group feature? Thanks.
Thanks. As pointed out by Tom, I need to follow up with a doc patch. I will
get that done in the next few days.show_group was a plpgsql function -- do you want a backend C version, or if
not, where would it go, contrib?
Oh, good point. We can't assume plpgsql will be in all databases. Seems
we do need a C version that can be installed via initdb. Then we can
hook it into psql \?.
--
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
Joe Conway writes:
show_group was a plpgsql function -- do you want a backend C version, or if
not, where would it go, contrib?
Please don't name a function "show" unless it creates output (rather than
returning a value).
--
Peter Eisentraut peter_e@gmx.net
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
Peter Eisentraut wrote:
Joe Conway writes:
show_group was a plpgsql function -- do you want a backend C version, or if
not, where would it go, contrib?Please don't name a function "show" unless it creates output (rather than
returning a value).
OK -- I'll come up with something better and pitch it to the list.
Joe
Peter Eisentraut wrote:
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 is starting to apply the stuff he held back for 7.4 to HEAD now, so this
is only applicable to 7.4devel.
Joe