How do I pass a list of values in postgres function without using array

Question

I need to create a function where the input parameter is a list of values with a single column:

Column1
Abc
Xyz
Mno
    
    

Create or replace function dummy(my_list <list of 3 values>) returns refcursor as
    .......
    $$
       Open ref for select * from table1 where col1 in (my_list);

       Return ref
    End;
    

Can someone please let me know how I can do this without using an array?

Also please let me know how I can call it in pgAdmin.

EDIT : Below is the code I am trying:

CREATE TYPE public.row1 AS
    (col1 character varying);

CREATE OR REPLACE FUNCTION public.xyz(
    i_a character varying,
    i_b row1[] DEFAULT array[NULL]::row1[])
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
ref refcursor='o_c'; -- Declare a cursor variable
BEGIN
    OPEN ref FOR
    select * from es_forecast_period_ngf a where a.fiscal_quarter_name=i_a and a.fiscal_period in any(i_b.col1);
RETURN ref; -- Return the cursor to the caller
END;
$BODY$;
    
    calling mechanism :


BEGIN;
    SELECT xyz('Q3 FY2031',array[row('Q3-M1'),row('Q3-M2')]::row1[]);
    FETCH ALL in "o_c";
    COMMIT;
    
    error while compiling:


ERROR:  syntax error at or near "any"
    LINE 13: ... a.fiscal_quarter_name=i_a and a.fiscal_period in any(i_b.co...
                                                                  ^
    SQL state: 42601
    Character: 375

Answer 1

You could use a variadic function (this example uses integer and is written in SQL, but that makes no difference):

CREATE FUNCTION dummy(VARIADIC args integer[]) RETURNS bigint
   LANGUAGE sql AS
'SELECT count(*)
FROM generate_series(1, 9) AS i
WHERE i = ANY (args)';

SELECT dummy(1, 5, 12);

 dummy 
-------
     2
(1 row)

SELECT dummy(1, 2, 5, 6, 9);

 dummy 
-------
     5
(1 row)

Such a function can be called with any number of arguments, but they all have to have the same type (unless you want to code C).

The values passed to such a variadic argument (which must be the last in the argument list) form an array inside the function body.

Answer 2

Another idea is to pass the list of values as text that can be cast to array. Details on array syntax here.
Assuming that the list consists of text values then:

create or replace function dummy(my_list text) returns refcursor as
........
open ref for select * from table1 where col1 = any(('{'||my_list||'}')::text[]);

Here you always pass the list of values as text.
If integer values then ('{'||my_list||'}')::text[] becomes ('{'||my_list||'}')::integer[] and so on for other data types. Please also note that the list of values text needs to be properly escaped.