使用postgres函数中的IN子句执行更新查询

Question

我正在尝试创建一个PostgreSQL函数，它将更新行列表。因此，我想以一个文本列表[]作为参数，并使用IN子句来执行查询。此外，我想得到更新的行数作为一个返回值。这对我有效：

CREATE FUNCTION set_mail_isbeingused(list_of_mail_names text[]) RETURNS void AS $$
    BEGIN
        FOR i in 1 .. array_upper(list_of_mail_names,1)
            LOOP
                UPDATE mail_isbeingused set isbeingused = 'true' where mailname = list_of_mail_names[i];
            END LOOP;
    END;
    $$
LANGUAGE plpgsql;

但我想用in子句在一个更新查询中执行它。

CREATE FUNCTION set_mail_isbeingused(list_of_mail_names text[]) RETURNS void AS $$
    BEGIN
        UPDATE mail_isbeingused set isbeingused = 'true' where mailname in list_of_mail_names;
    END;
    $$
LANGUAGE plpgsql;

这就是我一直想做的。有人能帮我解决这个问题吗？

Answer 1

您需要在数组中使用ANY运算符。不能使用IN

要获得更新的行数，请使用GET DIAGNOSTICS

CREATE FUNCTION set_mail_isbeingused(list_of_mail_names text[]) 
  RETURNS integer --<< you need to change the return type
AS $$
DECLARE
  l_rows integer;
BEGIN
  UPDATE mail_isbeingused 
     set isbeingused = true 
  where mailname = ANY (list_of_mail_names);
  GET DIAGNOSTICS l_rows = ROW_COUNT; --<< get the number of affected rows
  RETURN l_rows;
END;
$$
LANGUAGE plpgsql;

布尔常量不需要放在单引号中true是布尔值'true'是字符串常量。