Skip to content

Commit 1fcb977

Browse files
alvherrealvherre
committed
Add generate_subscripts, a series-generation function which generates an
array's subscripts. Pavel Stehule, some editorialization by me.
1 parent a1d479f commit 1fcb977

File tree

7 files changed

+253
-6
lines changed

7 files changed

+253
-6
lines changed

‎doc/src/sgml/array.sgml

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.65 2008/04/27 04:33:27 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.66 2008/04/28 14:48:57 alvherre Exp $ -->
22

33
<sect1 id="arrays">
44
<title>Arrays</title>
@@ -542,6 +542,21 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
542542

543543
</para>
544544

545+
<para>
546+
Alternatively, the <function>generate_subscripts</> function can be used.
547+
For example:
548+
549+
<programlisting>
550+
SELECT * FROM
551+
(SELECT pay_by_quarter,
552+
generate_subscripts(pay_by_quarter, 1) AS s
553+
FROM sal_emp) AS foo
554+
WHERE pay_by_quarter[s] = 10000;
555+
</programlisting>
556+
557+
This function is described in <xref linkend="functions-srf-subscripts">.
558+
</para>
559+
545560
<tip>
546561
<para>
547562
Arrays are not sets; searching for specific array elements

‎doc/src/sgml/func.sgml

Lines changed: 96 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.433 2008/04/17 20:56:41 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.434 2008/04/28 14:48:57 alvherre Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -10613,7 +10613,8 @@ AND
1061310613
<para>
1061410614
This section describes functions that possibly return more than one row.
1061510615
Currently the only functions in this class are series generating functions,
10616-
as detailed in <xref linkend="functions-srf-series">.
10616+
as detailed in <xref linkend="functions-srf-series"> and
10617+
<xref linkend="functions-srf-subscripts">.
1061710618
</para>
1061810619

1061910620
<table id="functions-srf-series">
@@ -10691,6 +10692,99 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a);
1069110692
(3 rows)
1069210693
</programlisting>
1069310694
</para>
10695+
10696+
<table id="functions-srf-subscripts">
10697+
10698+
<indexterm>
10699+
<primary>generate_subscripts</primary>
10700+
</indexterm>
10701+
10702+
<title>Subscripts Generating Functions</title>
10703+
<tgroup cols="3">
10704+
<thead>
10705+
<row>
10706+
<entry>Function</entry>
10707+
<entry>Return Type</entry>
10708+
<entry>Description</entry>
10709+
</row>
10710+
</thead>
10711+
10712+
<tbody>
10713+
<row>
10714+
<entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>)</literal></entry>
10715+
<entry><type>setof int</type></entry>
10716+
<entry>
10717+
Generate a series comprising the given array's subscripts.
10718+
</entry>
10719+
</row>
10720+
10721+
<row>
10722+
<entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>, <parameter>reverse boolean</parameter>)</literal></entry>
10723+
<entry><type>setof int</type></entry>
10724+
<entry>
10725+
Generate a series comprising the given array's subscripts. When
10726+
<parameter>reverse</parameter> is true, the series is returned in
10727+
reverse order.
10728+
</entry>
10729+
</row>
10730+
10731+
</tbody>
10732+
</tgroup>
10733+
</table>
10734+
10735+
<para>
10736+
Zero rows are returned for arrays that do not have the requested dimension,
10737+
or for NULL arrays (but valid subscripts are returned for NULL array
10738+
elements.) Some examples follow:
10739+
<programlisting>
10740+
-- basic usage
10741+
select generate_subscripts('{NULL,1,NULL,2}'::int[], 1) as s;
10742+
s
10743+
---
10744+
1
10745+
2
10746+
3
10747+
4
10748+
(4 rows)
10749+
10750+
-- presenting an array, the subscript and the subscripted
10751+
-- value requires a subquery
10752+
select * from arrays;
10753+
a
10754+
--------------------
10755+
{-1,-2}
10756+
{100,200}
10757+
(2 rows)
10758+
10759+
select a as array, s as subscript, a[s] as value
10760+
from (select generate_subscripts(a, 1) as s, a from arrays) foo;
10761+
array | subscript | value
10762+
-----------+-----------+-------
10763+
{-1,-2} | 1 | -1
10764+
{-1,-2} | 2 | -2
10765+
{100,200} | 1 | 100
10766+
{100,200} | 2 | 200
10767+
(4 rows)
10768+
10769+
-- unnest a 2D array
10770+
create or replace function unnest2(anyarray)
10771+
returns setof anyelement as $$
10772+
select $1[i][j]
10773+
from generate_subscripts($1,1) g1(i),
10774+
generate_subscripts($1,2) g2(j);
10775+
$$ language sql immutable;
10776+
CREATE FUNCTION
10777+
postgres=# select * from unnest2(array[[1,2],[3,4]]);
10778+
unnest2
10779+
---------
10780+
1
10781+
2
10782+
3
10783+
4
10784+
(4 rows)
10785+
</programlisting>
10786+
</para>
10787+
1069410788
</sect1>
1069510789

1069610790
<sect1 id="functions-info">

‎src/backend/utils/adt/arrayfuncs.c

Lines changed: 85 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.143 2008/04/11 22:52:05 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.144 2008/04/28 14:48:57 alvherre Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -17,6 +17,7 @@
1717
#include <ctype.h>
1818

1919
#include "access/tupmacs.h"
20+
#include "funcapi.h"
2021
#include "libpq/pqformat.h"
2122
#include "parser/parse_coerce.h"
2223
#include "utils/array.h"
@@ -4231,3 +4232,86 @@ array_smaller(PG_FUNCTION_ARGS)
42314232

42324233
PG_RETURN_ARRAYTYPE_P(result);
42334234
}
4235+
4236+
4237+
typedef struct generate_subscripts_fctx
4238+
{
4239+
int4 lower;
4240+
int4 upper;
4241+
bool reverse;
4242+
} generate_subscripts_fctx;
4243+
4244+
/*
4245+
* generate_subscripts(array anyarray, dim int [, reverse bool])
4246+
* Returns all subscripts of the array for any dimension
4247+
*/
4248+
Datum
4249+
generate_subscripts(PG_FUNCTION_ARGS)
4250+
{
4251+
FuncCallContext *funcctx;
4252+
MemoryContext oldcontext;
4253+
generate_subscripts_fctx *fctx;
4254+
4255+
/* stuff done only on the first call of the function */
4256+
if (SRF_IS_FIRSTCALL())
4257+
{
4258+
ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
4259+
int reqdim = PG_GETARG_INT32(1);
4260+
int *lb,
4261+
*dimv;
4262+
4263+
/* create a function context for cross-call persistence */
4264+
funcctx = SRF_FIRSTCALL_INIT();
4265+
4266+
/* Sanity check: does it look like an array at all? */
4267+
if (ARR_NDIM(v) <= 0 || ARR_NDIM(v) > MAXDIM)
4268+
SRF_RETURN_DONE(funcctx);
4269+
4270+
/* Sanity check: was the requested dim valid */
4271+
if (reqdim <= 0 || reqdim > ARR_NDIM(v))
4272+
SRF_RETURN_DONE(funcctx);
4273+
4274+
/*
4275+
* switch to memory context appropriate for multiple function calls
4276+
*/
4277+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
4278+
fctx = (generate_subscripts_fctx *) palloc(sizeof(generate_subscripts_fctx));
4279+
4280+
lb = ARR_LBOUND(v);
4281+
dimv = ARR_DIMS(v);
4282+
4283+
fctx->lower = lb[reqdim - 1];
4284+
fctx->upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1;
4285+
fctx->reverse = (PG_NARGS() < 3) ? false : PG_GETARG_BOOL(2);
4286+
4287+
funcctx->user_fctx = fctx;
4288+
4289+
MemoryContextSwitchTo(oldcontext);
4290+
}
4291+
4292+
funcctx = SRF_PERCALL_SETUP();
4293+
4294+
fctx = funcctx->user_fctx;
4295+
4296+
if (fctx->lower <= fctx->upper)
4297+
{
4298+
if (!fctx->reverse)
4299+
SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->lower++));
4300+
else
4301+
SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->upper--));
4302+
}
4303+
else
4304+
/* done when there are no more elements left */
4305+
SRF_RETURN_DONE(funcctx);
4306+
}
4307+
4308+
/*
4309+
* generate_subscripts_nodir
4310+
* Implements the 2-argument version of generate_subscripts
4311+
*/
4312+
Datum
4313+
generate_subscripts_nodir(PG_FUNCTION_ARGS)
4314+
{
4315+
/* just call the other one -- it can handle both cases */
4316+
return generate_subscripts(fcinfo);
4317+
}

‎src/include/catalog/pg_proc.h

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.492 2008/04/17 20:56:41 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.493 2008/04/28 14:48:57 alvherre Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1010,6 +1010,11 @@ DATA(insert OID = 515 ( array_larger PGNSP PGUID 12 1 0 f f t f i 2 2277 "22
10101010
DESCR("larger of two");
10111011
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_ ));
10121012
DESCR("smaller of two");
1013+
DATA(insert OID = 1191 ( generate_subscripts PGNSP PGUID 12 1 1000 f f t t v 3 23 "2277 23 16" _null_ _null_ _null_ generate_subscripts - _null_ _null_ ));
1014+
DESCR("array subscripts generator");
1015+
DATA(insert OID = 1192 ( generate_subscripts PGNSP PGUID 12 1 1000 f f t t v 2 23 "2277 23" _null_ _null_ _null_ generate_subscripts_nodir - _null_ _null_ ));
1016+
DESCR("array subscripts generator");
1017+
10131018

10141019
DATA(insert OID = 760 ( smgrin PGNSP PGUID 12 1 0 f f t f s 1 210 "2275" _null_ _null_ _null_ smgrin - _null_ _null_ ));
10151020
DESCR("I/O");

‎src/include/utils/array.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@
4949
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
5050
* Portions Copyright (c) 1994, Regents of the University of California
5151
*
52-
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.66 2008/01/01 19:45:59 momjian Exp $
52+
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.67 2008/04/28 14:48:57 alvherre Exp $
5353
*
5454
*-------------------------------------------------------------------------
5555
*/
@@ -200,6 +200,8 @@ extern Datum array_lower(PG_FUNCTION_ARGS);
200200
extern Datum array_upper(PG_FUNCTION_ARGS);
201201
extern Datum array_larger(PG_FUNCTION_ARGS);
202202
extern Datum array_smaller(PG_FUNCTION_ARGS);
203+
extern Datum generate_subscripts(PG_FUNCTION_ARGS);
204+
extern Datum generate_subscripts_nodir(PG_FUNCTION_ARGS);
203205

204206
extern Datum array_ref(ArrayType *array, int nSubscripts, int *indx,
205207
int arraytyplen, int elmlen, bool elmbyval, char elmalign,

‎src/test/regress/expected/arrays.out

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -903,3 +903,33 @@ select c2[2].f2 from comptable;
903903
drop type _comptype;
904904
drop table comptable;
905905
drop type comptype;
906+
create or replace function unnest1(anyarray)
907+
returns setof anyelement as $$
908+
select $1[s] from generate_subscripts($1,1) g(s);
909+
$$ language sql immutable;
910+
create or replace function unnest2(anyarray)
911+
returns setof anyelement as $$
912+
select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
913+
generate_subscripts($1,2) g2(s2);
914+
$$ language sql immutable;
915+
select * from unnest1(array[1,2,3]);
916+
unnest1
917+
---------
918+
1
919+
2
920+
3
921+
(3 rows)
922+
923+
select * from unnest2(array[[1,2,3],[4,5,6]]);
924+
unnest2
925+
---------
926+
1
927+
2
928+
3
929+
4
930+
5
931+
6
932+
(6 rows)
933+
934+
drop function unnest1(anyarray);
935+
drop function unnest2(anyarray);

‎src/test/regress/sql/arrays.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -340,3 +340,20 @@ select c2[2].f2 from comptable;
340340
drop type _comptype;
341341
drop table comptable;
342342
drop type comptype;
343+
344+
create or replace function unnest1(anyarray)
345+
returns setof anyelement as $$
346+
select $1[s] from generate_subscripts($1,1) g(s);
347+
$$ language sql immutable;
348+
349+
create or replace function unnest2(anyarray)
350+
returns setof anyelement as $$
351+
select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
352+
generate_subscripts($1,2) g2(s2);
353+
$$ language sql immutable;
354+
355+
select * from unnest1(array[1,2,3]);
356+
select * from unnest2(array[[1,2,3],[4,5,6]]);
357+
358+
drop function unnest1(anyarray);
359+
drop function unnest2(anyarray);

0 commit comments

Comments
 (0)