本文介绍了如何将功能应用到Postgres的阵列列的每个元素?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
一个PG查询返回的数组。我想检索与格式化为3位小数每个元素。如何申请一个函数对数组中的每一个元素?类似下面的(错,显然) -
A Pg query returns an array. I would like to retrieve that with each element formatted to 3 decimal places. How can I apply a function to each element of an array? Something like the following (wrong, obviously) --
SELECT Round(ARRAY[1.53224,0.23411234], 2); {1.532, 0.234}我想我寻找类似Perl的地图功能。
推荐答案您可能需要创建一个存储功能。这里是做你所需要的一个:
You may need to create a stored function. Here is the one that does what you need:
CREATE OR REPLACE FUNCTION array_round(float[], int) RETURNS float[] AS $$ DECLARE arrFloats ALIAS FOR $1; roundParam ALIAS FOR $2; retVal float[]; BEGIN FOR I IN array_lower(arrFloats, 1)..array_upper(arrFloats, 1) LOOP retVal[I] := round(CAST(arrFloats[I] as numeric), roundParam); END LOOP; RETURN retVal; END; $$ LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT;然后调用是这样的:
Then call something like this:
# SELECT array_round(ARRAY[1.53224,0.23411234], 2); array_round ------------- {1.53,0.23} (1 row)更多推荐
如何将功能应用到Postgres的阵列列的每个元素?
发布评论