How to apply a function to each element of an array column in Postgres?

Solution 1:

First, turn the array into a set using unnest:

> SELECT n FROM unnest(ARRAY[1.53224,0.23411234]) AS n;
     n      
------------
    1.53224
 0.23411234
(2 rows)

Then, apply an expression to the column:

> SELECT ROUND(n, 2) FROM unnest(ARRAY[1.53224,0.23411234]) AS n;
 round 
-------
  1.53
  0.23
(2 rows)

Finally, use array_agg to turn the set back into an array:

> SELECT array_agg(ROUND(n, 2)) FROM unnest(ARRAY[1.53224,0.23411234]) AS n;
  array_agg  
-------------
 {1.53,0.23}
(1 row)

Solution 2:

postgres=# select array(select round(unnest(array[1.2,2.4,3,4])));
   array   
-----------
{1,2,3,4}
(1 row)

Solution 3:

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)