Postgres function with list argument and in clause
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 asRETURNS SETOF target_table
-
Assuming you actually want a
SET
of rows from yourtarget_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 withEXPLAIN ANALYZE
to see for yourself.
Or use the construct withunnest()
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[]);
Related videos on Youtube
user1756277
Updated on September 15, 2022Comments
-
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.