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.

awesome suggestions from everyone. I think I will go with the stored proc as I need to apply this kind of a `map` function all the time. It would be even greater if I could pass in a function to the stored proc, thereby making it into a factory stored proc that would convert it into a true `map` function. But, this will work for now. Thanks again, everyone. — punkish, Dec 21 '11 at 02:51
Re: passing in a function: You may be interested in http://stackoverflow.com/questions/8346065/function-as-parameter-to-another-function-in-postgres. (It's far from ideal, but you may get some use from it.) — ruakh, Dec 21 '11 at 03:27

score 140 · Answer 1 · answered Dec 21 '11 at 02:43

140

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)

answered Dec 21 '11 at 02:43

Joey Adams

41,996
18
86
115

3

And you could wrap that up into a user defined `round(int[], int)` function as well. – mu is too short Dec 21 '11 at 02:48
7

This is the most concise solution, and I like the clear way you construct it. – Edmund Dec 22 '11 at 21:43
Note that on an empty input array, arrag_agg() returns NULL, i.e. not an array. See the answer of @Pavel Stehule for a solution that always returns a (possibly empty) array: https://stackoverflow.com/a/8586492/19163 – vog Feb 27 '19 at 10:45
5

This is unsafe if you care about the array ordering. While PostgreSQL won't currently re-order this result, it's free to do so in future per the SQL spec. You **must** use `unnest ... with ordinality` (9.4+) – Craig Ringer Mar 21 '19 at 05:15

score 40 · Answer 2 · answered Dec 21 '11 at 07:43

40

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

answered Dec 21 '11 at 07:43

Pavel Stehule

42,331
5
91
94

There are some performance difference between `array_agg()` (see Joey's solution) and `array()` (this solution)? – Peter Krauss Mar 04 '18 at 18:05
2

@PeterKrauss I did benchmark now, and speed is same – Pavel Stehule Mar 04 '18 at 20:40
2

Note that on an empty input array, arrag_agg() returns NULL while array() returns an empty array. – vog Feb 27 '19 at 10:44
array() is also more convenient for the cases when you do NOT want to do aggregations (e.g., when you want to include some attributes from the parent row). – Andris Birkmanis Apr 11 '22 at 20:32

score 15 · Accepted Answer · answered Dec 21 '11 at 02:33

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)

Edmund · Answer 4 · 2011-12-22T21:42:34.230

0

You need to turn the array into a row set. For example, using generate_series:

SELECT ARRAY(SELECT ROUND(ARRAY[1.53224,0.23411234])[i], 2) FROM generate_series(1,2) AS s(i));

I know that's pretty ugly. There should be a helper function to make such mappings easier.

Perhaps something like (yes it's horrible, slow, and brittle dynamic code):

CREATE OR REPLACE FUNCTION map_with_arg(TEXT, ANYARRAY, TEXT)
RETURNS ANYARRAY
IMMUTABLE STRICT
LANGUAGE 'plpgsql' AS
$$
DECLARE
    i INTEGER;
    t TEXT;
    cmd TEXT;
BEGIN
    FOR i IN array_lower($2, 1) .. array_upper($2, 1) LOOP
        cmd := 'SELECT ('||quote_ident($1)||'('||quote_nullable($2[i])||', '||quote_nullable($3)||'))::TEXT';
        EXECUTE cmd INTO t;
        $2[i] := t;
    END LOOP;
    RETURN $2;
END;
$$;

select map_with_arg('repeat', array['can','to']::TEXT[], '2');
 map_with_arg
---------------
 {cancan,toto}

Update It occurs to me that we could use a single dynamic statement for the whole loop. This could mitigate some of the performance concerns.

CREATE OR REPLACE FUNCTION map_with_arg(TEXT, ANYARRAY, TEXT)
RETURNS ANYARRAY
IMMUTABLE STRICT
LANGUAGE 'plpgsql' AS
$$
DECLARE
    cmd TEXT;
    rv TEXT;
BEGIN
    cmd := 'SELECT ARRAY(SELECT (' || quote_ident($1)||'($1[i], '||quote_nullable($3)||'))::TEXT FROM generate_subscripts($1, 1) AS gs(i))';
    EXECUTE cmd USING $2 INTO rv;
    RETURN rv;
END;
$$;

edited Dec 22 '11 at 21:42

answered Dec 21 '11 at 02:34

Edmund

10,533
3
39
57

1

Dynamic SQL inside loop is really terrible idea. Don't do it in plpgsql. This is relative static language and patterns from perl, python or other scripting languages cannot by applied here. – Pavel Stehule Dec 21 '11 at 07:46
1

Yes, that's what my sentence before the code says. I resorted to it in this admittedly whimsical example because I want to pass the function to be called into plpgsql. That's a fundamental abstraction in programming and a useful thing to be able to do in any language. Of course it's a pain that we need to do it as a string and then construct a new statement dynamically, but that's something to be taken into consideration if you ever want to write a general mapping function. The alternative is to have to write 100 non-general mapping functions. – Edmund Dec 21 '11 at 21:22
if you need this form of abstraction, then use a own C modules. PL/pgSQL is not language for abstract coding. Any not effective code on database side can descrease performance significantly. map_with_args should be relative simply implemented in C – Pavel Stehule Dec 22 '11 at 06:11
2

A builtin or C module with a few generic map functions would be helpful. But I respectfully disagree that one should *always* resort to writing a C module for something like this. Performance isn't the overriding concern in every single case. And the economics of writing a C module vs using a builtin language are different for a seasoned PostgreSQL hacker than for the rest of us. ;-) – Edmund Dec 22 '11 at 21:23
For small projects you can have true. But not for something larger than small. Speed of database is usually significant - and with fast database, you can use a simple and small environment. And when you have slow database, then increasing speed of application is terrible hard and terrible expensive. And what should be better tasks for C modules than simple and abstract tool. A price on C coder is not all - if you have less there, then you will have higher elsewhere - admin time, tuning time, and less bussness from slower application. – Pavel Stehule Dec 23 '11 at 00:33
That first statement `SELECT ARRAY(SELECT ROUND(ARRAY[1.53224,0.23411234])[i], 2) FROM generate_series(1,2) AS s(i));` is completely wrong. Have you even tried it? – Lukas Eder Sep 21 '16 at 12:06

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

4 Answers4

Linked

Related