Postgres function with list argument and in clause

19,382

Solution 1

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;

Solution 2

PL/pgSQL function

Filling in the gaps in your question, it could look like this:

CREATE OR REPLACE FUNCTION func1(_arr integer[])
  RETURNS SETOF target_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   target_table
    WHERE  link_id = l.link_id;
END LOOP;

END
$func$;
  • If you want to return the result of SELECT * FROM my_schema_and_table; you have to go about this whole function differently. Declare it as RETURNS SETOF target_table

  • Assuming you actually want a SET of rows from your target_table, not an array?

  • Rewrite the IN construct to = ANY(_arr). That's the way to use an array parameter directly. Internally, PostgreSQL rewrites an IN expression to = ANY() anyway. Test with EXPLAIN ANALYZE to see for yourself.
    Or use the construct with unnest() and join to the resulting table like @Clodoaldo demonstrates. That's faster with long arrays.

Simplify to plain SQL function

The above is still pointlessly contrived. Simplify to a SQL function doing the same:

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

Call:

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

Related videos on Youtube

user1756277
Author by

user1756277

Updated on September 15, 2022

Comments

  • user1756277
    user1756277 about 1 year

    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.