I have problem to create function with following definition:

CREATE OR REPLACE FUNCTION COPY_ACTIONS_V1_TEST(iActionMasterIdList IN text)
RETURNS TEXT AS $$
DECLARE

    vRequestedId ACTIONS.ACTION_ID%TYPE;
    result_aid_list TEXT;

    cur_action CURSOR FOR 
        select act.ACTION_ID
        FROM   ACTIONS act
        WHERE  1=1
        and act.action_id IN iActionMasterIdList;
        
BEGIN 
        OPEN cur_action;
        LOOP
            FETCH cur_action INTO vActionMasterId;
            if not FOUND THEN
                return result_aid_list;
            end if;
   
        END LOOP; 
        CLOSE cur_action;

END;
$$ LANGUAGE plpgsql;

I want to pass text variable as '123,124,126' to this function and I want to search for this ids in table. I tried many options like:

1. act.action_id = any(string_to_array(iActionMasterIdList))
2. act.action_id = any('{' || iActionMasterIdList || '}')

But nothing works. Each throws different error. I am not expert in postgres, can you give advice?

Thank you

CodePudding user response：

string_to_array - returns array elements in text type. If action_id is integer, then you can use casting types. Example:

and act.action_id::text = any(string_to_array(iActionMasterIdList, ','))