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.000000000 +0100
--- ./doc/src/sgml/func.sgml 2008-03-24 19:03:43.000000000 +0100
***************
*** 10562,10569 ****
<para>
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 <xref linkend="functions-srf-series">.
</para>
<table id="functions-srf-series">
--- 10562,10567 ----
***************
*** 10641,10646 ****
--- 10639,10697 ----
(3 rows)
</programlisting>
</para>
+
+ <table id="functions-srf-subscripts">
+ <title>Subscripts Generating Functions</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>)</literal></entry>
+ <entry><type>setof int</type></entry>
+ <entry>
+ Generate a series of array's subscripts.
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>, <parameter>reverse boolean</parameter>)</literal></entry>
+ <entry><type>setof int</type></entry>
+ <entry>
+ Generate a series of array's subscripts. When <parameter>reverse</parameter> is true, then series is reverse order.
+ </entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <programlisting>
+ -- 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)
+ </programlisting>
+
+
</sect1>
<sect1 id="functions-info">
*** ./src/backend/utils/adt/arrayfuncs.c.orig 2008-03-24 17:38:45.000000000 +0100
--- ./src/backend/utils/adt/arrayfuncs.c 2008-03-24 18:12:35.000000000 +0100
***************
*** 16,21 ****
--- 16,22 ----
#include <ctype.h>
+ #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
+ {
+ int4 lower;
+ int4 upper;
+ bool reverse;
+ } 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--));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
*** ./src/include/catalog/pg_proc.h.orig 2008-03-24 17:39:12.000000000 +0100
--- ./src/include/catalog/pg_proc.h 2008-03-24 18:17:01.000000000 +0100
***************
*** 1015,1020 ****
--- 1015,1025 ----
DESCR("larger of two");
DATA(insert OID = 516 ( array_smaller PGNSP PGUID 12 1 0 f f t f i 2 2277 "2277 2277" _null_ _null_ _null_ array_smaller - _null_ _null_ ));
DESCR("smaller of two");
+ DATA(insert OID = 1191 ( generate_subscripts PGNSP PGUID 12 1 1000 f f t t v 3 23 "2277 23 16" _null_ _null_ _null_ array_subscripts_direction - _null_ _null_ ));
+ DESCR("array subscripts generator");
+ DATA(insert OID = 1192 ( generate_subscripts PGNSP PGUID 12 1 1000 f f t t v 2 23 "2277 23" _null_ _null_ _null_ array_subscripts - _null_ _null_ ));
+ DESCR("array subscripts generator");
+
DATA(insert OID = 760 ( smgrin PGNSP PGUID 12 1 0 f f t f s 1 210 "2275" _null_ _null_ _null_ smgrin - _null_ _null_ ));
DESCR("I/O");
*** ./src/include/utils/array.h.orig 2008-03-24 17:39:36.000000000 +0100
--- ./src/include/utils/array.h 2008-03-24 18:18:12.000000000 +0100
***************
*** 200,205 ****
--- 200,207 ----
extern Datum array_upper(PG_FUNCTION_ARGS);
extern Datum array_larger(PG_FUNCTION_ARGS);
extern Datum array_smaller(PG_FUNCTION_ARGS);
+ extern Datum array_subscripts(PG_FUNCTION_ARGS);
+ extern Datum array_subscripts_direction(PG_FUNCTION_ARGS);
extern Datum array_ref(ArrayType *array, int nSubscripts, int *indx,
int arraytyplen, int elmlen, bool elmbyval, char elmalign,
*** ./src/test/regress/expected/arrays.out.orig 2008-03-21 13:17:42.000000000 +0100
--- ./src/test/regress/expected/arrays.out 2008-03-24 17:42:13.000000000 +0100
***************
*** 903,905 ****
--- 903,935 ----
drop type _comptype;
drop table comptable;
drop type comptype;
+ create or replace function array_expand(anyarray)
+ returns setof anyelement as $$
+ select $1[s] from generate_subscripts($1,1) g(s);
+ $$ language sql immutable;
+ create or replace function array_expand2(anyarray)
+ returns setof anyelement as $$
+ select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
+ generate_subscripts($1,2) g2(s2);
+ $$ language sql immutable;
+ select * from array_expand(array[1,2,3]);
+ array_expand
+ --------------
+ 1
+ 2
+ 3
+ (3 rows)
+
+ select * from array_expand2(array[[1,2,3],[4,5,6]]);
+ array_expand2
+ ---------------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ (6 rows)
+
+ drop function array_expand(anyarray);
+ drop function array_expand2(anyarray);
*** ./src/test/regress/sql/arrays.sql.orig 2008-03-21 13:17:43.000000000 +0100
--- ./src/test/regress/sql/arrays.sql 2008-03-24 17:42:13.000000000 +0100
***************
*** 340,342 ****
--- 340,359 ----
drop type _comptype;
drop table comptable;
drop type comptype;
+
+ create or replace function array_expand(anyarray)
+ returns setof anyelement as $$
+ select $1[s] from generate_subscripts($1,1) g(s);
+ $$ language sql immutable;
+
+ create or replace function array_expand2(anyarray)
+ returns setof anyelement as $$
+ select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
+ generate_subscripts($1,2) g2(s2);
+ $$ language sql immutable;
+
+ select * from array_expand(array[1,2,3]);
+ select * from array_expand2(array[[1,2,3],[4,5,6]]);
+
+ drop function array_expand(anyarray);
+ drop function array_expand2(anyarray);
-
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
