postgresql - 函数中的 for 循环无效

Question

我正在尝试使以下功能正常工作:

CREATE OR REPLACE FUNCTION validate_count(devices TEXT[], campaign_id INTEGER) RETURNS void AS $$
  DECLARE
    devices_array TEXT[]  := devices;
    devices_count INTEGER := array_length(devices, 1);
    row_id        INTEGER := campaign_id;
  BEGIN
    FOR device IN unnest(devices_array) LOOP
      IF my_count('my_table', device, row_id) != 1;
        RAISE EXCEPTION 'invalid_count %', row_id
      ENDIF
    END LOOP;
  END
$$ LANGUAGE plpgsql;

my_count 是一个返回 INTEGER 的工作函数。

定义失败并出现错误:

ERROR:  syntax error at or near "unnest"
LINE 7:           FOR device IN unnest(devices_array) LOOP

你能发现问题吗？谢谢!

我打算按如下方式调用该函数:

select validate_count('{foo, bar}', 1)

Answer 1

使用 FOREACH循环:

CREATE OR REPLACE FUNCTION validate_count(devices TEXT[], campaign_id INTEGER) 
  RETURNS void 
AS 
$$
DECLARE
  device text;
  devices_count INTEGER := array_length(devices, 1);
BEGIN
  FOREACH device IN ARRAY devices LOOP
    IF my_count('my_table', device, campaign_id) <> 1 then
      RAISE EXCEPTION 'invalid_count %', campaign_id;
    END IF;
  END LOOP;
END
$$ 
LANGUAGE plpgsql;