Splitting Comma Separated String In Pl/pgsql Function

I am trying to write a function that takes an ID as an input and update some fields on that given ID. So far, it looks like this: CREATE FUNCTION update_status(p_id character varyi

Solution 1:

Blue Star already mentioned that there is a built-in function to convert a comma separated string into an array.

But I would suggest to not pass a comma separated string to begin with. If you want to pass a variable number of IDs use a variadic parameter.

You also don't need to first run a SELECT, you can ask the system how many rows were updated after the UPDATE statement.

CREATEFUNCTION update_status(p_status text, p_id variadic integer[]) 
  RETURNScharactervaryingLANGUAGE plpgsql
AS
$$
DECLARE
  v_row_count bigintDEFAULT0;
BEGINUPDATE test
  SET status     = p_status,
      updated_by ='admin'WHERE user_id =any (p_id);
    
  get diagnostics v_row_count = row_count;
  if v_row_count =0thenreturn'User not found';
  end if;
  
  return concat(v_row_count, ' users updated');
END
$$;

You can use it like this:

selectupdate_status('active', 1);
selectupdate_status('active', 5, 8, 42);

---

If for some reason, you "have" to pass this as a single argument, use a real array instead:

CREATE FUNCTIONupdate_status(p_status text, p_id integer[]) 

Then pass it like this:

selectupdate_status('active', array[5,8,42]);

or

selectupdate_status('active', '{5,8,42}');

Solution 2:

There's a function for that, see docs.

SELECT string_to_array('str1,str2,str3,str4', ',');

    string_to_array
-----------------------
 {str1,str2,str3,str4}

Note that once it's an array, you'll want your condition to look like this -

WHERE user_id =ANY(string_to_array(p_id, ',');