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

Question

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}

I guess I am looking for something like Perl's map function.

Answer 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)