It may be simple but somehow, I still don't get it correct. How to pass array of strings into a function and use it in "IN" statement in SQL
CREATE OR REPLACE function test_function(array_para text)
RETURNS TABLE (data text)
AS $$
begin
return query select col1 from my_table where my_table.col2 in(array_para)
END
$$
LANGUAGE 'plpgsql';
and how can I call it? may be --
select test_function('''ABC'', ''MITT''')
You need to declare the parameter as and array, e.g. text[]. You also don't need PL/pgSQL for that:
CREATE OR REPLACE function test_function(array_para text[])
RETURNS TABLE (data text)
AS $$
select col1
from my_table
where my_table.col2 = any (array_para)
$$
LANGUAGE sql;
Then call it with:
select *
from test_function(array['ABC', 'MITT']);
Another option is to use a variadic parameter:
CREATE OR REPLACE function test_function(variadic array_para text[])
RETURNS TABLE (data text)
AS $$
select col1
from my_table
where my_table.col2 = any (array_para)
$$
LANGUAGE sql;
Then you can call it with:
select *
from test_function('ABC', 'MITT', 'DEF');