Postgres function with list argument and in clause

Question

How to create a function which takes as argument integer[] parameter and executing query with IN clause with this parameter in loop. In loop I want execute next select and result of this query I would like return.

Something like that:

CREATE OR REPLACE FUNCTION function_which_i_want(my_argument integer[]) RETURNS my_schema_and_table[] AS
$BODY$
DECLARE
result my_schema_and_table[];
BEGIN
FOR l IN SELECT * FROM table2 WHERE id in (my_argument) LOOP
SELECT * FROM my_schema_and_table;
END LOOP;
END;
...

I want to get union of each select in loop. one huge joined result. Is this possible? Please help.

Answer 1

PL/pgSQL function

It could look like this:

CREATE OR REPLACE FUNCTION func1(_arr integer[])
  RETURNS SETOF my_schema_and_table
  LANGUAGE plpgsql AS
$func$
DECLARE
   l record;
BEGIN
   FOR l IN
      SELECT *
      FROM   lookup_table
      WHERE  some_id = ANY(_arr)
   LOOP
      RETURN QUERY
      SELECT *
      FROM   my_schema_and_table
      WHERE  link_id = l.link_id;
   END LOOP;
END
$func$;

Assuming you actually want a SET of rows from your my_schema_and_table, not an array? To return the result of SELECT * FROM my_schema_and_table, declare the function as RETURNS SETOF my_schema_and_table

Rewrite the IN construct to = ANY(_arr). That's the way to use an array parameter directly. Logically equivalent.
Or use unnest() and join to the resulting table like @Clodoaldo demonstrates. That can be faster with long arrays.

Simplify to plain SQL function

This simple SQL function does the same:

CREATE OR REPLACE FUNCTION func2(_arr integer[])
  RETURNS SETOF my_schema_and_table
  LANGUAGE sql AS
$func$
SELECT t.*
FROM  (SELECT unnest($1) AS some_id) x
JOIN   lookup_table l USING (some_id) 
JOIN   my_schema_and_table t USING (link_id);
$func$

Assuming both tables have link_id.

Call:

SELECT * FROM func2('{21,31}'::int[]);

Answer 2

CREATE OR REPLACE FUNCTION function_which_i_want(my_argument integer[])
RETURNS my_schema_and_table[] AS
$BODY$
DECLARE
result my_schema_and_table[];
BEGIN

for l in 
    select t.*
    from
        table2 t
        inner join
        unnest(my_argument) m(id) on m.id = t.id
loop
    SELECT * FROM my_schema_and_table;
END LOOP;
END;