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

Commit84a4256

Browse files
committed
Add array_remove() and array_replace() functions.
These functions support removing or replacing array element value(s)matching a given search value. Although intended mainly to support afuture array-foreign-key feature, they seem useful in their own right.Marco Nenciarini and Gabriele Bartolini, reviewed by Alex Hunsaker
1 parentf995125 commit84a4256

File tree

7 files changed

+415
-1
lines changed

7 files changed

+415
-1
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10316,6 +10316,12 @@ SELECT NULLIF(value, '(none)') ...
1031610316
<indexterm>
1031710317
<primary>array_prepend</primary>
1031810318
</indexterm>
10319+
<indexterm>
10320+
<primary>array_remove</primary>
10321+
</indexterm>
10322+
<indexterm>
10323+
<primary>array_replace</primary>
10324+
</indexterm>
1031910325
<indexterm>
1032010326
<primary>array_to_string</primary>
1032110327
</indexterm>
@@ -10432,6 +10438,29 @@ SELECT NULLIF(value, '(none)') ...
1043210438
<entry><literal>array_prepend(1, ARRAY[2,3])</literal></entry>
1043310439
<entry><literal>{1,2,3}</literal></entry>
1043410440
</row>
10441+
<row>
10442+
<entry>
10443+
<literal>
10444+
<function>array_remove</function>(<type>anyarray</type>, <type>anyelement</type>)
10445+
</literal>
10446+
</entry>
10447+
<entry><type>anyarray</type></entry>
10448+
<entry>remove all elements equal to the given value from the array
10449+
(array must be one-dimensional)</entry>
10450+
<entry><literal>array_remove(ARRAY[1,2,3,2], 2)</literal></entry>
10451+
<entry><literal>{1,3}</literal></entry>
10452+
</row>
10453+
<row>
10454+
<entry>
10455+
<literal>
10456+
<function>array_replace</function>(<type>anyarray</type>, <type>anyelement</type>, <type>anyelement</type>)
10457+
</literal>
10458+
</entry>
10459+
<entry><type>anyarray</type></entry>
10460+
<entry>replace each array element equal to the given value with a new value</entry>
10461+
<entry><literal>array_replace(ARRAY[1,2,5,4], 5, 3)</literal></entry>
10462+
<entry><literal>{1,2,3,4}</literal></entry>
10463+
</row>
1043510464
<row>
1043610465
<entry>
1043710466
<literal>

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

Lines changed: 306 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,11 @@ static ArrayType *create_array_envelope(int ndims, int *dimv, int *lbv, int nbyt
124124
staticArrayType*array_fill_internal(ArrayType*dims,ArrayType*lbs,
125125
Datumvalue,boolisnull,Oidelmtype,
126126
FunctionCallInfofcinfo);
127+
staticArrayType*array_replace_internal(ArrayType*array,
128+
Datumsearch,boolsearch_isnull,
129+
Datumreplace,boolreplace_isnull,
130+
boolremove,Oidcollation,
131+
FunctionCallInfofcinfo);
127132

128133

129134
/*
@@ -5174,3 +5179,304 @@ array_unnest(PG_FUNCTION_ARGS)
51745179
SRF_RETURN_DONE(funcctx);
51755180
}
51765181
}
5182+
5183+
5184+
/*
5185+
* array_replace/array_remove support
5186+
*
5187+
* Find all array entries matching (not distinct from) search/search_isnull,
5188+
* and delete them if remove is true, else replace them with
5189+
* replace/replace_isnull. Comparisons are done using the specified
5190+
* collation. fcinfo is passed only for caching purposes.
5191+
*/
5192+
staticArrayType*
5193+
array_replace_internal(ArrayType*array,
5194+
Datumsearch,boolsearch_isnull,
5195+
Datumreplace,boolreplace_isnull,
5196+
boolremove,Oidcollation,
5197+
FunctionCallInfofcinfo)
5198+
{
5199+
ArrayType*result;
5200+
Oidelement_type;
5201+
Datum*values;
5202+
bool*nulls;
5203+
int*dim;
5204+
intndim;
5205+
intnitems,
5206+
nresult;
5207+
inti;
5208+
int32nbytes=0;
5209+
int32dataoffset;
5210+
boolhasnulls;
5211+
inttyplen;
5212+
booltypbyval;
5213+
chartypalign;
5214+
char*arraydataptr;
5215+
bits8*bitmap;
5216+
intbitmask;
5217+
boolchanged= false;
5218+
TypeCacheEntry*typentry;
5219+
FunctionCallInfoDatalocfcinfo;
5220+
5221+
element_type=ARR_ELEMTYPE(array);
5222+
ndim=ARR_NDIM(array);
5223+
dim=ARR_DIMS(array);
5224+
nitems=ArrayGetNItems(ndim,dim);
5225+
5226+
/* Return input array unmodified if it is empty */
5227+
if (nitems <=0)
5228+
returnarray;
5229+
5230+
/*
5231+
* We can't remove elements from multi-dimensional arrays, since the
5232+
* result might not be rectangular.
5233+
*/
5234+
if (remove&&ndim>1)
5235+
ereport(ERROR,
5236+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5237+
errmsg("removing elements from multidimensional arrays is not supported")));
5238+
5239+
/*
5240+
* We arrange to look up the equality function only once per series of
5241+
* calls, assuming the element type doesn't change underneath us.
5242+
*/
5243+
typentry= (TypeCacheEntry*)fcinfo->flinfo->fn_extra;
5244+
if (typentry==NULL||
5245+
typentry->type_id!=element_type)
5246+
{
5247+
typentry=lookup_type_cache(element_type,
5248+
TYPECACHE_EQ_OPR_FINFO);
5249+
if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
5250+
ereport(ERROR,
5251+
(errcode(ERRCODE_UNDEFINED_FUNCTION),
5252+
errmsg("could not identify an equality operator for type %s",
5253+
format_type_be(element_type))));
5254+
fcinfo->flinfo->fn_extra= (void*)typentry;
5255+
}
5256+
typlen=typentry->typlen;
5257+
typbyval=typentry->typbyval;
5258+
typalign=typentry->typalign;
5259+
5260+
/*
5261+
* Detoast values if they are toasted. The replacement value must be
5262+
* detoasted for insertion into the result array, while detoasting the
5263+
* search value only once saves cycles.
5264+
*/
5265+
if (typlen==-1)
5266+
{
5267+
if (!search_isnull)
5268+
search=PointerGetDatum(PG_DETOAST_DATUM(search));
5269+
if (!replace_isnull)
5270+
replace=PointerGetDatum(PG_DETOAST_DATUM(replace));
5271+
}
5272+
5273+
/* Prepare to apply the comparison operator */
5274+
InitFunctionCallInfoData(locfcinfo,&typentry->eq_opr_finfo,2,
5275+
collation,NULL,NULL);
5276+
5277+
/* Allocate temporary arrays for new values */
5278+
values= (Datum*)palloc(nitems*sizeof(Datum));
5279+
nulls= (bool*)palloc(nitems*sizeof(bool));
5280+
5281+
/* Loop over source data */
5282+
arraydataptr=ARR_DATA_PTR(array);
5283+
bitmap=ARR_NULLBITMAP(array);
5284+
bitmask=1;
5285+
hasnulls= false;
5286+
nresult=0;
5287+
5288+
for (i=0;i<nitems;i++)
5289+
{
5290+
Datumelt;
5291+
boolisNull;
5292+
booloprresult;
5293+
boolskip= false;
5294+
5295+
/* Get source element, checking for NULL */
5296+
if (bitmap&& (*bitmap&bitmask)==0)
5297+
{
5298+
isNull= true;
5299+
/* If searching for NULL, we have a match */
5300+
if (search_isnull)
5301+
{
5302+
if (remove)
5303+
{
5304+
skip= true;
5305+
changed= true;
5306+
}
5307+
elseif (!replace_isnull)
5308+
{
5309+
values[nresult]=replace;
5310+
isNull= false;
5311+
changed= true;
5312+
}
5313+
}
5314+
}
5315+
else
5316+
{
5317+
isNull= false;
5318+
elt=fetch_att(arraydataptr,typbyval,typlen);
5319+
arraydataptr=att_addlength_datum(arraydataptr,typlen,elt);
5320+
arraydataptr= (char*)att_align_nominal(arraydataptr,typalign);
5321+
5322+
if (search_isnull)
5323+
{
5324+
/* no match possible, keep element */
5325+
values[nresult]=elt;
5326+
}
5327+
else
5328+
{
5329+
/*
5330+
* Apply the operator to the element pair
5331+
*/
5332+
locfcinfo.arg[0]=elt;
5333+
locfcinfo.arg[1]=search;
5334+
locfcinfo.argnull[0]= false;
5335+
locfcinfo.argnull[1]= false;
5336+
locfcinfo.isnull= false;
5337+
oprresult=DatumGetBool(FunctionCallInvoke(&locfcinfo));
5338+
if (!oprresult)
5339+
{
5340+
/* no match, keep element */
5341+
values[nresult]=elt;
5342+
}
5343+
else
5344+
{
5345+
/* match, so replace or delete */
5346+
changed= true;
5347+
if (remove)
5348+
skip= true;
5349+
else
5350+
{
5351+
values[nresult]=replace;
5352+
isNull=replace_isnull;
5353+
}
5354+
}
5355+
}
5356+
}
5357+
5358+
if (!skip)
5359+
{
5360+
nulls[nresult]=isNull;
5361+
if (isNull)
5362+
hasnulls= true;
5363+
else
5364+
{
5365+
/* Update total result size */
5366+
nbytes=att_addlength_datum(nbytes,typlen,values[nresult]);
5367+
nbytes=att_align_nominal(nbytes,typalign);
5368+
/* check for overflow of total request */
5369+
if (!AllocSizeIsValid(nbytes))
5370+
ereport(ERROR,
5371+
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
5372+
errmsg("array size exceeds the maximum allowed (%d)",
5373+
(int)MaxAllocSize)));
5374+
}
5375+
nresult++;
5376+
}
5377+
5378+
/* advance bitmap pointer if any */
5379+
if (bitmap)
5380+
{
5381+
bitmask <<=1;
5382+
if (bitmask==0x100)
5383+
{
5384+
bitmap++;
5385+
bitmask=1;
5386+
}
5387+
}
5388+
}
5389+
5390+
/*
5391+
* If not changed just return the original array
5392+
*/
5393+
if (!changed)
5394+
{
5395+
pfree(values);
5396+
pfree(nulls);
5397+
returnarray;
5398+
}
5399+
5400+
/* Allocate and initialize the result array */
5401+
if (hasnulls)
5402+
{
5403+
dataoffset=ARR_OVERHEAD_WITHNULLS(ndim,nresult);
5404+
nbytes+=dataoffset;
5405+
}
5406+
else
5407+
{
5408+
dataoffset=0;/* marker for no null bitmap */
5409+
nbytes+=ARR_OVERHEAD_NONULLS(ndim);
5410+
}
5411+
result= (ArrayType*)palloc0(nbytes);
5412+
SET_VARSIZE(result,nbytes);
5413+
result->ndim=ndim;
5414+
result->dataoffset=dataoffset;
5415+
result->elemtype=element_type;
5416+
memcpy(ARR_DIMS(result),ARR_DIMS(array),2*ndim*sizeof(int));
5417+
5418+
if (remove)
5419+
{
5420+
/* Adjust the result length */
5421+
ARR_DIMS(result)[0]=nresult;
5422+
}
5423+
5424+
/* Insert data into result array */
5425+
CopyArrayEls(result,
5426+
values,nulls,nresult,
5427+
typlen,typbyval,typalign,
5428+
false);
5429+
5430+
pfree(values);
5431+
pfree(nulls);
5432+
5433+
returnresult;
5434+
}
5435+
5436+
/*
5437+
* Remove any occurrences of an element from an array
5438+
*
5439+
* If used on a multi-dimensional array this will raise an error.
5440+
*/
5441+
Datum
5442+
array_remove(PG_FUNCTION_ARGS)
5443+
{
5444+
ArrayType*array;
5445+
Datumsearch=PG_GETARG_DATUM(1);
5446+
boolsearch_isnull=PG_ARGISNULL(1);
5447+
5448+
if (PG_ARGISNULL(0))
5449+
PG_RETURN_NULL();
5450+
array=PG_GETARG_ARRAYTYPE_P(0);
5451+
5452+
array=array_replace_internal(array,
5453+
search,search_isnull,
5454+
(Datum)0, true,
5455+
true,PG_GET_COLLATION(),
5456+
fcinfo);
5457+
PG_RETURN_ARRAYTYPE_P(array);
5458+
}
5459+
5460+
/*
5461+
* Replace any occurrences of an element in an array
5462+
*/
5463+
Datum
5464+
array_replace(PG_FUNCTION_ARGS)
5465+
{
5466+
ArrayType*array;
5467+
Datumsearch=PG_GETARG_DATUM(1);
5468+
boolsearch_isnull=PG_ARGISNULL(1);
5469+
Datumreplace=PG_GETARG_DATUM(2);
5470+
boolreplace_isnull=PG_ARGISNULL(2);
5471+
5472+
if (PG_ARGISNULL(0))
5473+
PG_RETURN_NULL();
5474+
array=PG_GETARG_ARRAYTYPE_P(0);
5475+
5476+
array=array_replace_internal(array,
5477+
search,search_isnull,
5478+
replace,replace_isnull,
5479+
false,PG_GET_COLLATION(),
5480+
fcinfo);
5481+
PG_RETURN_ARRAYTYPE_P(array);
5482+
}

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201206171
56+
#defineCATALOG_VERSION_NO201207111
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -867,6 +867,10 @@ DATA(insert OID = 1286 ( array_fill PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 22
867867
DESCR("array constructor with value");
868868
DATA(insertOID=2331 (unnestPGNSPPGUID12110000fffftti102283"2277"_null__null__null__null_array_unnest_null__null__null_ ));
869869
DESCR("expand array to set of rows");
870+
DATA(insertOID=3167 (array_removePGNSPPGUID121000ffffffi202277"2277 2283"_null__null__null__null_array_remove_null__null__null_ ));
871+
DESCR("remove any occurrences of an element from an array");
872+
DATA(insertOID=3168 (array_replacePGNSPPGUID121000ffffffi302277"2277 2283 2283"_null__null__null__null_array_replace_null__null__null_ ));
873+
DESCR("replace any occurrences of an element in an array");
870874
DATA(insertOID=2333 (array_agg_transfnPGNSPPGUID121000ffffffi202281"2281 2283"_null__null__null__null_array_agg_transfn_null__null__null_ ));
871875
DESCR("aggregate transition function");
872876
DATA(insertOID=2334 (array_agg_finalfnPGNSPPGUID121000ffffffi102277"2281"_null__null__null__null_array_agg_finalfn_null__null__null_ ));

‎src/include/utils/array.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -211,6 +211,8 @@ extern Datum generate_subscripts_nodir(PG_FUNCTION_ARGS);
211211
externDatumarray_fill(PG_FUNCTION_ARGS);
212212
externDatumarray_fill_with_lower_bounds(PG_FUNCTION_ARGS);
213213
externDatumarray_unnest(PG_FUNCTION_ARGS);
214+
externDatumarray_remove(PG_FUNCTION_ARGS);
215+
externDatumarray_replace(PG_FUNCTION_ARGS);
214216

215217
externDatumarray_ref(ArrayType*array,intnSubscripts,int*indx,
216218
intarraytyplen,intelmlen,boolelmbyval,charelmalign,

0 commit comments

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