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

Commitc889ebc

Browse files
committed
Implement the basic form of UNNEST, ie unnest(anyarray) returns setof
anyelement. This lacks the WITH ORDINALITY option, as well as the multipleinput arrays option added in the most recent SQL specs. But it's still apretty useful subset of the spec's functionality, and it is enough toallow obsoleting contrib/intagg.
1 parent5b9453b commitc889ebc

File tree

7 files changed

+193
-6
lines changed

7 files changed

+193
-6
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.459 2008/11/13 23:01:09 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.460 2008/11/14 00:51:46 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -9482,6 +9482,17 @@ SELECT NULLIF(value, '(none)') ...
94829482
<entry><literal>string_to_array('xx~^~yy~^~zz', '~^~')</literal></entry>
94839483
<entry><literal>{xx,yy,zz}</literal></entry>
94849484
</row>
9485+
<row>
9486+
<entry>
9487+
<literal>
9488+
<function>unnest</function>(<type>anyarray</type>)
9489+
</literal>
9490+
</entry>
9491+
<entry><type>setof anyelement</type></entry>
9492+
<entry>expand an array to a set of rows</entry>
9493+
<entry><literal>unnest(ARRAY[1,2])</literal></entry>
9494+
<entry><literal>1</literal><para><literal>2</literal></para> (2 rows)</entry>
9495+
</row>
94859496
</tbody>
94869497
</tgroup>
94879498
</table>

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

Lines changed: 105 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.149 2008/11/12 13:09:27 petere Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.150 2008/11/14 00:51:46 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -4635,3 +4635,107 @@ array_fill_internal(ArrayType *dims, ArrayType *lbs,
46354635

46364636
returnresult;
46374637
}
4638+
4639+
4640+
/*
4641+
* UNNEST
4642+
*/
4643+
Datum
4644+
array_unnest(PG_FUNCTION_ARGS)
4645+
{
4646+
typedefstruct
4647+
{
4648+
ArrayType*arr;
4649+
intnextelem;
4650+
intnumelems;
4651+
char*elemdataptr;/* this moves with nextelem */
4652+
bits8*arraynullsptr;/* this does not */
4653+
int16elmlen;
4654+
boolelmbyval;
4655+
charelmalign;
4656+
}array_unnest_fctx;
4657+
4658+
FuncCallContext*funcctx;
4659+
array_unnest_fctx*fctx;
4660+
MemoryContextoldcontext;
4661+
4662+
/* stuff done only on the first call of the function */
4663+
if (SRF_IS_FIRSTCALL())
4664+
{
4665+
ArrayType*arr=PG_GETARG_ARRAYTYPE_P(0);
4666+
4667+
/* create a function context for cross-call persistence */
4668+
funcctx=SRF_FIRSTCALL_INIT();
4669+
4670+
/*
4671+
* switch to memory context appropriate for multiple function calls
4672+
*/
4673+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
4674+
4675+
/* allocate memory for user context */
4676+
fctx= (array_unnest_fctx*)palloc(sizeof(array_unnest_fctx));
4677+
4678+
/*
4679+
* Initialize state. Note we assume that the originally passed
4680+
* array will stick around for the whole call series.
4681+
*/
4682+
fctx->arr=arr;
4683+
fctx->nextelem=0;
4684+
fctx->numelems=ArrayGetNItems(ARR_NDIM(arr),ARR_DIMS(arr));
4685+
4686+
fctx->elemdataptr=ARR_DATA_PTR(arr);
4687+
fctx->arraynullsptr=ARR_NULLBITMAP(arr);
4688+
4689+
get_typlenbyvalalign(ARR_ELEMTYPE(arr),
4690+
&fctx->elmlen,
4691+
&fctx->elmbyval,
4692+
&fctx->elmalign);
4693+
4694+
funcctx->user_fctx=fctx;
4695+
MemoryContextSwitchTo(oldcontext);
4696+
}
4697+
4698+
/* stuff done on every call of the function */
4699+
funcctx=SRF_PERCALL_SETUP();
4700+
fctx=funcctx->user_fctx;
4701+
4702+
if (fctx->nextelem<fctx->numelems)
4703+
{
4704+
intoffset=fctx->nextelem++;
4705+
Datumelem;
4706+
4707+
/*
4708+
* Check for NULL array element
4709+
*/
4710+
if (array_get_isnull(fctx->arraynullsptr,offset))
4711+
{
4712+
fcinfo->isnull= true;
4713+
elem= (Datum)0;
4714+
/* elemdataptr does not move */
4715+
}
4716+
else
4717+
{
4718+
/*
4719+
* OK, get the element
4720+
*/
4721+
char*ptr=fctx->elemdataptr;
4722+
4723+
fcinfo->isnull= false;
4724+
elem=ArrayCast(ptr,fctx->elmbyval,fctx->elmlen);
4725+
4726+
/*
4727+
* Advance elemdataptr over it
4728+
*/
4729+
ptr=att_addlength_pointer(ptr,fctx->elmlen,ptr);
4730+
ptr= (char*)att_align_nominal(ptr,fctx->elmalign);
4731+
fctx->elemdataptr=ptr;
4732+
}
4733+
4734+
SRF_RETURN_NEXT(funcctx,elem);
4735+
}
4736+
else
4737+
{
4738+
/* do when there is no more left */
4739+
SRF_RETURN_DONE(funcctx);
4740+
}
4741+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.505 2008/11/13 15:59:50 petere Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.506 2008/11/14 00:51:46 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200811131
56+
#defineCATALOG_VERSION_NO200811132
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 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.527 2008/11/13 15:59:50 petere Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.528 2008/11/14 00:51:46 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1022,6 +1022,8 @@ DATA(insert OID = 1193 ( array_fill PGNSP PGUID 12 1 0 0 f f f f i 2 2277 "2283
10221022
DESCR("array constructor with value");
10231023
DATA(insertOID=1286 (array_fillPGNSPPGUID12100ffffi32277"2283 1007 1007"_null__null__null_array_fill_with_lower_bounds_null__null__null_ ));
10241024
DESCR("array constructor with value");
1025+
DATA(insertOID=2331 (unnestPGNSPPGUID1211000fftti12283"2277"_null__null__null_array_unnest_null__null__null_ ));
1026+
DESCR("expand array to set of rows");
10251027
DATA(insertOID=2333 (array_agg_transfnPGNSPPGUID12100ffffi22281"2281 2283"_null__null__null_array_agg_transfn_null__null__null_ ));
10261028
DESCR("array_agg transition function");
10271029
DATA(insertOID=2334 (array_agg_finalfnPGNSPPGUID12100ffffi12277"2281"_null__null__null_array_agg_finalfn_null__null__null_ ));

‎src/include/utils/array.h

Lines changed: 2 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.71 2008/11/13 15:59:50 petere Exp $
52+
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.72 2008/11/14 00:51:47 tgl Exp $
5353
*
5454
*-------------------------------------------------------------------------
5555
*/
@@ -206,6 +206,7 @@ extern Datum generate_subscripts(PG_FUNCTION_ARGS);
206206
externDatumgenerate_subscripts_nodir(PG_FUNCTION_ARGS);
207207
externDatumarray_fill(PG_FUNCTION_ARGS);
208208
externDatumarray_fill_with_lower_bounds(PG_FUNCTION_ARGS);
209+
externDatumarray_unnest(PG_FUNCTION_ARGS);
209210

210211
externDatumarray_ref(ArrayType*array,intnSubscripts,int*indx,
211212
intarraytyplen,intelmlen,boolelmbyval,charelmalign,

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

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1161,3 +1161,65 @@ select array_agg(unique1) from tenk1 where unique1 < -15;
11611161

11621162
(1 row)
11631163

1164+
select unnest(array[1,2,3]);
1165+
unnest
1166+
--------
1167+
1
1168+
2
1169+
3
1170+
(3 rows)
1171+
1172+
select * from unnest(array[1,2,3]);
1173+
unnest
1174+
--------
1175+
1
1176+
2
1177+
3
1178+
(3 rows)
1179+
1180+
select unnest(array[1,2,3,4.5]::float8[]);
1181+
unnest
1182+
--------
1183+
1
1184+
2
1185+
3
1186+
4.5
1187+
(4 rows)
1188+
1189+
select unnest(array[1,2,3,4.5]::numeric[]);
1190+
unnest
1191+
--------
1192+
1
1193+
2
1194+
3
1195+
4.5
1196+
(4 rows)
1197+
1198+
select unnest(array[1,2,3,null,4,null,null,5,6]);
1199+
unnest
1200+
--------
1201+
1
1202+
2
1203+
3
1204+
1205+
4
1206+
1207+
1208+
5
1209+
6
1210+
(9 rows)
1211+
1212+
select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);
1213+
unnest
1214+
--------
1215+
1
1216+
2
1217+
3
1218+
1219+
4
1220+
1221+
1222+
5
1223+
6
1224+
(9 rows)
1225+

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -402,3 +402,10 @@ select array_agg(nullif(ten, 4)) from tenk1 where unique1 < 15;
402402
select cardinality(array_agg(unique1))from tenk1where unique1<15;
403403
select array_agg(unique1)from (select*from tenk1order by unique1asc)as tabwhere unique1<15;
404404
select array_agg(unique1)from tenk1where unique1<-15;
405+
406+
select unnest(array[1,2,3]);
407+
select*from unnest(array[1,2,3]);
408+
select unnest(array[1,2,3,4.5]::float8[]);
409+
select unnest(array[1,2,3,4.5]::numeric[]);
410+
select unnest(array[1,2,3,null,4,null,null,5,6]);
411+
select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);

0 commit comments

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