如何将功能应用到Postgres的阵列列的每个元素？ [英] How to apply a function to each element of an array column in 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的阵列列的每个元素？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！