如何将功能应用到Postgres的阵列列的每个元素？

本文介绍了如何将功能应用到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)