Hello
This patch contains generate_subscripts functions, that generate
series of array's subscripts of some dimension:
postgres=#
create or replace function unnest2(anyarray)
returns setof anyelement as $$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$ language sql immutable;
postgres=# select * from unnest2(array[[1,2],[3,4]]);
unnest2
-
1
2
3
4
(4 rows)
Proposal: http://archives.postgresql.org/pgsql-hackers/2007-10/msg00874.php
Regards
Pavel Stehule
*** ./doc/src/sgml/func.sgml.orig 2008-03-24 18:01:54.0 +0100
--- ./doc/src/sgml/func.sgml 2008-03-24 19:03:43.0 +0100
***
*** 10562,10569
This section describes functions that possibly return more than one row.
-Currently the only functions in this class are series generating functions,
-as detailed in .
--- 10562,10567
***
*** 10641,10646
--- 10639,10697
(3 rows)
+
+
+Subscripts Generating Functions
+
+
+
+ Function
+ Return Type
+ Description
+
+
+
+
+
+ generate_subscripts(array annyarray, dim int)
+ setof int
+
+Generate a series of array's subscripts.
+
+
+
+
+ generate_subscripts(array annyarray, dim int, reverse boolean)
+ setof int
+
+Generate a series of array's subscripts. When reverse is true, then series is reverse order.
+
+
+
+
+
+
+
+
+ -- unnest 2D array
+ create or replace function unnest2(anyarray)
+ returns setof anyelement as $$
+ select $1[i][j]
+from generate_subscripts($1,1) g1(i),
+ generate_subscripts($1,2) g2(j);
+ $$ language sql immutable;
+ CREATE FUNCTION
+ postgres=# select * from unnest2(array[[1,2],[3,4]]);
+ unnest2
+ -
+1
+2
+3
+4
+ (4 rows)
+
+
+
*** ./src/backend/utils/adt/arrayfuncs.c.orig 2008-03-24 17:38:45.0 +0100
--- ./src/backend/utils/adt/arrayfuncs.c 2008-03-24 18:12:35.0 +0100
***
*** 16,21
--- 16,22
#include
+ #include "funcapi.h"
#include "access/tupmacs.h"
#include "libpq/pqformat.h"
#include "parser/parse_coerce.h"
***
*** 96,101
--- 97,108
int typlen, bool typbyval, char typalign);
static int array_cmp(FunctionCallInfo fcinfo);
+ typedef struct generate_subscripts_fctx
+ {
+ int4lower;
+ int4upper;
+ boolreverse;
+ } generate_subscripts_fctx;
/*
* array_in :
***
*** 4237,4239
--- 4244,4315
PG_RETURN_ARRAYTYPE_P(result);
}
+
+ /*
+ * array_subscripts(array anyarray, dim int, reverse bool)
+ * returns all subscripts of array for any dimension
+ */
+ Datum
+ array_subscripts_direction(PG_FUNCTION_ARGS)
+ {
+ return array_subscripts(fcinfo);
+ }
+
+ Datum
+ array_subscripts(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+ generate_subscripts_fctx *fctx;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
+ int reqdim = PG_GETARG_INT32(1);
+ int *lb, *dimv;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Sanity check: does it look like an array at all? */
+ if (ARR_NDIM(v) <= 0 || ARR_NDIM(v) > MAXDIM)
+ SRF_RETURN_DONE(funcctx);
+
+ /* Sanity check: was the requested dim valid */
+ if (reqdim <= 0 || reqdim > ARR_NDIM(v))
+ SRF_RETURN_DONE(funcctx);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ fctx = (generate_subscripts_fctx *) palloc(sizeof(generate_subscripts_fctx));
+
+ lb = ARR_LBOUND(v);
+ dimv = ARR_DIMS(v);
+
+ fctx->lower = lb[reqdim - 1];
+ fctx->upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1;
+ fctx->reverse = (PG_NARGS() < 3) ? false : PG_GETARG_BOOL(2);
+
+ funcctx->user_fctx = fctx;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+
+ fctx = funcctx->user_fctx;
+
+ if (fctx->lower <= fctx->upper)
+ {
+ if (!fctx->reverse)
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->lower++));
+ else
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->upper--));
+
