Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1fcb977

Browse files
committed
Add generate_subscripts, a series-generation function which generates an
array's subscripts.Pavel Stehule, some editorialization by me.
1 parenta1d479f commit1fcb977

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+
typedefstructgenerate_subscripts_fctx
4238+
{
4239+
int4lower;
4240+
int4upper;
4241+
boolreverse;
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+
MemoryContextoldcontext;
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+
intreqdim=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+
returngenerate_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(insertOID=516 (array_smallerPGNSPPGUID1210fftfi22277"2277 2277"_null__null__null_array_smaller-_null__null_ ));
10121012
DESCR("smaller of two");
1013+
DATA(insertOID=1191 (generate_subscriptsPGNSPPGUID1211000ffttv323"2277 23 16"_null__null__null_generate_subscripts-_null__null_ ));
1014+
DESCR("array subscripts generator");
1015+
DATA(insertOID=1192 (generate_subscriptsPGNSPPGUID1211000ffttv223"2277 23"_null__null__null_generate_subscripts_nodir-_null__null_ ));
1016+
DESCR("array subscripts generator");
1017+
10131018

10141019
DATA(insertOID=760 (smgrinPGNSPPGUID1210fftfs1210"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
externDatumarray_upper(PG_FUNCTION_ARGS);
201201
externDatumarray_larger(PG_FUNCTION_ARGS);
202202
externDatumarray_smaller(PG_FUNCTION_ARGS);
203+
externDatumgenerate_subscripts(PG_FUNCTION_ARGS);
204+
externDatumgenerate_subscripts_nodir(PG_FUNCTION_ARGS);
203205

204206
externDatumarray_ref(ArrayType*array,intnSubscripts,int*indx,
205207
intarraytyplen,intelmlen,boolelmbyval,charelmalign,

‎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
droptype _comptype;
341341
droptable comptable;
342342
droptype comptype;
343+
344+
create or replacefunctionunnest1(anyarray)
345+
returns setof anyelementas $$
346+
select $1[s]from generate_subscripts($1,1) g(s);
347+
$$ language sql immutable;
348+
349+
create or replacefunctionunnest2(anyarray)
350+
returns setof anyelementas $$
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+
dropfunction unnest1(anyarray);
359+
dropfunction unnest2(anyarray);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp