Postgresql – Map functions over array with Postgres

postgresql

Is there any way in Postgres to apply a function over a results of an array (or any sort of collection)?

For example if I have a function create function add1... that increments a number by one, is there any way to do something like:

select map add1 array[1,5,8...];

and getting back

array [2,6,9...]

?

I am not sure if it has any sense over a select as it can map a function on the single result directly:

select add1(x) from X;

Also, is there any concept of laziness here? All the functions will be 'strictly' applied?

Best Answer

If you are merely looking to increment each element of an input array, this simple function is one method of doing it.

CREATE OR REPLACE FUNCTION add1 (int[]) RETURNS int[] AS
$func$
DECLARE
    e       int;
    new_arr int[] := '{}';
BEGIN
    FOREACH e IN ARRAY $1 LOOP
        e := e + 1;
        new_arr := new_arr || e;
    END LOOP;
    RETURN new_arr;
END;
$func$ LANGUAGE plpgsql STABLE;

SELECT add1(array[1,5,8,15]::int[]);
    add1
------------
 {2,6,9,16}

Or queried from your table

select id, array_col, add1(array_col) from yourtable;
 id |      arr      |     add1
----+---------------+---------------
  1 | {1,3,6,9}     | {2,4,7,10}
  2 | {10,20,30,40} | {11,21,31,41}

Plain SQL version of the function:

CREATE OR REPLACE FUNCTION add1(int[]) RETURNS int[] AS
$func$
    SELECT array_agg(vals)
    FROM (select unnest($1) + 1 as vals) AS x;
$func$ LANGUAGE sql STABLE;
Related Question