Map functions over array with Postgres

Question

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?

Answer 1

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;