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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
