Hello The attached patch contains a implementation of "subscripts" function. The functionality of this function is same like generate_subscripts function, but it's based for iteration from plpgsql's for-in-array.
Regards Pavel Stehule
*** ./doc/src/sgml/func.sgml.orig 2010-09-29 10:40:40.000000000 +0200 --- ./doc/src/sgml/func.sgml 2010-09-30 21:25:07.294900077 +0200 *************** *** 9940,9945 **** --- 9940,9956 ---- <row> <entry> <literal> + <function>subscripts</function>(<type>anyarray</type>, <type>int</type> <optional>, <type>boolean</type></optional>) + </literal> + </entry> + <entry><type>int[]</type></entry> + <entry>returns array of subscripts of entered array, when third parameter is true, then result is reveresed</entry> + <entry><literal>subscripts(ARRAY[10,20,NULL,30,1], 1, true)</literal></entry> + <entry><literal>{5,4,3,2,1}</literal></entry> + </row> + <row> + <entry> + <literal> <function>unnest</function>(<type>anyarray</type>) </literal> </entry> *** ./src/backend/utils/adt/arrayfuncs.c.orig 2010-09-29 10:40:40.000000000 +0200 --- ./src/backend/utils/adt/arrayfuncs.c 2010-09-30 20:18:50.339024900 +0200 *************** *** 17,22 **** --- 17,23 ---- #include <ctype.h> #include "funcapi.h" + #include "catalog/pg_type.h" #include "libpq/pqformat.h" #include "parser/parse_coerce.h" #include "utils/array.h" *************** *** 4337,4342 **** --- 4338,4401 ---- PG_RETURN_ARRAYTYPE_P(result); } + /* + * subscripts(array anyarray, dim int [, reverse bool] + * Returns all subscripts of the array for any dimension as array + */ + Datum + subscripts(PG_FUNCTION_ARGS) + { + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int reqdim = PG_GETARG_INT32(1); + int *lb, + *dimv; + int lower; + int upper; + bool reverse; + ArrayBuildState *astate = NULL; + + /* 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); + + lower = lb[reqdim - 1]; + upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1; + reverse = (PG_NARGS() < 3) ? false : PG_GETARG_BOOL(2); + + while (lower <= upper) + { + Datum value; + + if (!reverse) + value = Int32GetDatum(lower++); + else + value = Int32GetDatum(upper--); + + astate = accumArrayResult(astate, + value, false, + INT4OID, CurrentMemoryContext); + } + + PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext)); + } + + /* + * subscripts_nodir + * Implements the 2-argument version of subscripts + */ + Datum + subscripts_nodir(PG_FUNCTION_ARGS) + { + /* just call the other one -- it can handle both cases */ + return subscripts(fcinfo); + } typedef struct generate_subscripts_fctx { *** ./src/include/catalog/pg_proc.h.orig 2010-09-29 10:40:40.000000000 +0200 --- ./src/include/catalog/pg_proc.h 2010-09-30 21:01:31.043900248 +0200 *************** *** 1043,1048 **** --- 1043,1052 ---- DESCR("array subscripts generator"); DATA(insert OID = 1192 ( generate_subscripts PGNSP PGUID 12 1 1000 0 f f f t t i 2 0 23 "2277 23" _null_ _null_ _null_ _null_ generate_subscripts_nodir _null_ _null_ _null_ )); DESCR("array subscripts generator"); + DATA(insert OID = 3811 ( subscripts PGNSP PGUID 12 1 0 0 f f f t f i 3 0 1007 "2277 23 16" _null_ _null_ _null_ _null_ subscripts _null_ _null_ _null_ )); + DESCR("array subscripts generator"); + DATA(insert OID = 3812 ( subscripts PGNSP PGUID 12 1 0 0 f f f t f i 2 0 1007 "2277 23" _null_ _null_ _null_ _null_ subscripts_nodir _null_ _null_ _null_ )); + DESCR("array subscripts generator"); DATA(insert OID = 1193 ( array_fill PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2277 "2283 1007" _null_ _null_ _null_ _null_ array_fill _null_ _null_ _null_ )); DESCR("array constructor with value"); DATA(insert OID = 1286 ( array_fill PGNSP PGUID 12 1 0 0 f f f f f i 3 0 2277 "2283 1007 1007" _null_ _null_ _null_ _null_ array_fill_with_lower_bounds _null_ _null_ _null_ )); *** ./src/include/utils/array.h.orig 2010-09-29 10:40:40.000000000 +0200 --- ./src/include/utils/array.h 2010-09-30 19:53:29.215024927 +0200 *************** *** 202,207 **** --- 202,209 ---- extern Datum array_length(PG_FUNCTION_ARGS); extern Datum array_larger(PG_FUNCTION_ARGS); extern Datum array_smaller(PG_FUNCTION_ARGS); + extern Datum subscripts(PG_FUNCTION_ARGS); + extern Datum subscripts_nodir(PG_FUNCTION_ARGS); extern Datum generate_subscripts(PG_FUNCTION_ARGS); extern Datum generate_subscripts_nodir(PG_FUNCTION_ARGS); extern Datum array_fill(PG_FUNCTION_ARGS); *** ./src/test/regress/expected/arrays.out.orig 2010-09-29 10:40:40.000000000 +0200 --- ./src/test/regress/expected/arrays.out 2010-09-30 21:06:15.000000000 +0200 *************** *** 1286,1288 **** --- 1286,1313 ---- [5:5]={"(42,43)"} (1 row) + -- subscripts tests + select subscripts(array[1,3,4,10],1); + subscripts + ------------ + {1,2,3,4} + (1 row) + + select subscripts(array[1,3,4,10],2); + subscripts + ------------ + + (1 row) + + select subscripts(array[1,3,4,10],1, true); + subscripts + ------------ + {4,3,2,1} + (1 row) + + select subscripts(array[1,3,4,10],2, true); + subscripts + ------------ + + (1 row) + *** ./src/test/regress/sql/arrays.sql.orig 2010-09-29 10:40:40.000000000 +0200 --- ./src/test/regress/sql/arrays.sql 2010-09-30 21:06:00.602900207 +0200 *************** *** 426,428 **** --- 426,434 ---- select * from t1; update t1 set f1[5].q2 = 43; select * from t1; + + -- subscripts tests + select subscripts(array[1,3,4,10],1); + select subscripts(array[1,3,4,10],2); + select subscripts(array[1,3,4,10],1, true); + select subscripts(array[1,3,4,10],2, true);
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers