4

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?

  • Only one what I could to imagine: dbfiddle.uk/… – Abelisto Aug 13 '17 at 13:14
  • @Abelisto can't you rework that to use oids instead map(regprocedure, anyarray, out anyarray) – Evan Carroll Aug 13 '17 at 18:51
  • @EvanCarroll It was my second attempt, just after the regproc, but it caused the ugly calling syntax. – Abelisto Aug 13 '17 at 18:56
2

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;
  • I would recommend to always use STABLE for function for do not change data in db - for 10,000 calls it 92ms instead 118ms – Roman Tkachuk Aug 13 '17 at 22:18
  • Good point. STABLE has been added - which got me wondering if a plain SQL version wouldn't work as well. I've added both, though the original pl/pgsql version tested a bit faster than the SQL version for me. – bma Aug 13 '17 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.