Splitting comma separated string in PL/pgSQL function

Question

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 varying,
                              p_status character varying DEFAULT NULL::character varying) RETURNS character varying
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_row_count bigint DEFAULT 0;
    v_result    varchar(255);
BEGIN
    
    IF p_id IS NOT NULL THEN
        SELECT count(user_id)
        INTO v_row_count
        FROM test
        WHERE user_id = p_id;
    END IF;

    IF v_row_count <= 0 THEN
        v_result = 'User not found';
        RETURN v_result;

    ELSE
        IF p_id NOT LIKE '%,%' THEN
            UPDATE test
            SET status     = p_status,
                updated_by = 'admin'
            WHERE user_id IN (p_id);
        ELSE
            --Here comes split and pass multiple IDs into an IN() operator
        END IF;
    END IF;
    
END
$$;

ALTER FUNCTION update_status(varchar, varchar) OWNER TO postgres;

Now, it is supposed to accept only one ID at a time but I wonder if I can get it to also accept multiple IDs -maybe even hundreds- once by splitting that single string into an array of IDs if it has a comma delimiter, then pass those to an IN() operator. How can I get split a string into an array so I can feed it to an IN() operator?

score 6 · Accepted Answer · 2020-09-26T21:01:02.027

6

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.

CREATE FUNCTION update_status(p_status text, p_id variadic integer[]) 
  RETURNS character varying
  LANGUAGE plpgsql
AS
$$
DECLARE
  v_row_count bigint DEFAULT 0;
BEGIN
  UPDATE 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 = 0 then 
    return 'User not found';
  end if;
  
  return concat(v_row_count, ' users updated');
END
$$;

You can use it like this:

select update_status('active', 1);
select update_status('active', 5, 8, 42);

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

CREATE FUNCTION update_status(p_status text, p_id integer[])

Then pass it like this:

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

or

select update_status('active', '{5,8,42}');

edited Sep 26 '20 at 21:01

answered Sep 26 '20 at 20:05

This looks great but I have to pass it in with a single argument like `select update_status('active', '5, 8, 42');` – Özenç B. Sep 26 '20 at 20:22
@ÖzençB.: why do you "have" to pass it as a single argument? – Sep 26 '20 at 20:47
The function will be invoked by a tool which needs a fixed number of parameters to work – Özenç B. Sep 26 '20 at 20:53
@a_horse_with_no_name This is a way better answer than mine, thanks for teaching me some new things! – Blue Star Sep 26 '20 at 21:45
2

@ÖzençB.: If you have to pass a single argument, you can still use a `VARIADIC` function (to have both options at your disposal). See: https://stackoverflow.com/a/19204560/939860 or https://stackoverflow.com/a/17978831/939860 – Erwin Brandstetter Sep 27 '20 at 23:16
@ErwinBrandstetter This makes variadic types a lot more viable in my usa case, I will definitely give it a try. Thank you – Özenç B. Sep 28 '20 at 11:06

Blue Star · Answer 2 · 2020-09-26T21:47:41.190

1

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, ',');

edited Sep 26 '20 at 21:47

answered Sep 26 '20 at 19:19

Blue Star

1,932
1
10
11

2

If you don't need a regex `string_to_array()` is typically faster – Sep 26 '20 at 19:47

Splitting comma separated string in PL/pgSQL function

2 Answers2

Linked