Feike Steenbergen

In the first example you will execute the function as many times as there are users.
In the second example you will only execute the function once.

The following test sample will show you this:

CREATE TABLE users(id integer primary key);
INSERT INTO users SELECT * FROM generate_series(1,10);

CREATE FUNCTION get_involved_user_ids_in_projects_for_user_id(int)
RETURNS int[]
LANGUAGE plpgsql
AS
$BODY$
DECLARE
    result int[];
BEGIN
    RAISE NOTICE '.';
    SELECT array_agg(i)
      INTO result
      FROM (SELECT (random()*9)::int+1 FROM generate_series(1,(random()*3)::int)) AS sub(i);
    RETURN result;
END;
$BODY$
VOLATILE;

SELECT "users".* FROM "users" WHERE "users"."id" = ANY(get_involved_user_ids_in_projects_for_user_id(3));

SELECT "users".* FROM "users"  WHERE "users"."id" IN (select(unnest(get_involved_user_ids_in_projects_for_user_id(3))));

You will see it is executed 10 times in the first statement and only once in the second.

What you could do, it you are sure it is correct is to change the function into a stable function (instead of the default volatile). Declaring a function to be stable is saying that it will return consistent results within one sql statement.

It is explained in detail here:

http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/xfunc-volatility.html