我正在尝试使以下函数正常工作:
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)
使用 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;
需要修复一些事情才能使其有效。
FOR
循环需要在unnest
之前SELECT
,即:
FOR device IN SELECT unnest(devices_array) LOOP
您需要在顶部声明device
,例如:
DECLARE device RECORD;
IF
语句需要一个THEN
而不是一个;
,即:
IF my_count('my_table', device, row_id) != 1 THEN
RAISE EXCEPTION
语句需要在行尾加一个分号,即:
RAISE EXCEPTION 'invalid_count %', row_id;
IF
语句的END
应END IF;
。
LOOP
语句的END
应END LOOP;
。
这是最终结果:
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;
device RECORD;
BEGIN
FOR device IN SELECT unnest(devices_array) LOOP
IF my_count('my_table', device, row_id) != 1 THEN
RAISE EXCEPTION 'invalid_count %', row_id;
END IF;
END LOOP;
END
$$ LANGUAGE plpgsql;