在 PL/pgSQL 函数中拆分逗号分隔的字符串 [英] Splitting comma separated string in PL/pgSQL function
问题描述
我正在尝试编写一个函数,该函数将 ID 作为输入并更新该给定 ID 上的某些字段.到目前为止,它看起来像这样:
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;
现在,它应该一次只接受一个 ID,但我想知道我是否可以通过将单个字符串拆分为一个 ID 数组(如果它有逗号)来接受多个 ID(甚至可能是数百个)一次分隔符,然后将它们传递给 IN()
运算符.如何将字符串拆分为数组,以便将其提供给 IN()
运算符?
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?
推荐答案
蓝星已经提到有一个内置函数可以将逗号分隔的字符串转换为数组.
Blue Star already mentioned that there is a built-in function to convert a comma separated string into an array.
但我建议不要以逗号分隔的字符串开头.如果要传递可变数量的 ID,请使用 variadic
参数.
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.
你也不需要先运行一个SELECT,你可以在UPDATE语句之后询问系统更新了多少行.
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
$$;
你可以这样使用它:
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[])
然后像这样传递它:
select update_status('active', array[5,8,42]);
或
select update_status('active', '{5,8,42}');
这篇关于在 PL/pgSQL 函数中拆分逗号分隔的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!