Postgres FOR循环出现语法错误
Postgres FOR循环出现语法错误
提问于 2010-05-15 16:33:17
我在postgres函数中遇到了一个问题：
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint)
RETURNS int AS $$
DECLARE catNumber int DEFAULT 0;
DECLARE cat RECORD;
BEGIN
WITH RECURSIVE children(categoryid,category_fk) AS (
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
FOR cat IN SELECT * FROM children LOOP
IF EXISTS (SELECT 1 FROM b2m.repoobject_tab WHERE category_fk = cat.categoryid) THEN
catNumber = catNumber +1
END IF;
END LOOP;
RETURN catNumber;
END;
$$ LANGUAGE 'plpgsql';我得到了一个错误：
FEHLER: Syntax error near »FOR«
LINE 1: ...dren WHERE children.categoryid = c1.category_fk ) FOR $2 I...回答 2
Stack Overflow用户
回答已采纳
发布于 2010-05-15 17:07:55
语法错误是因为它希望WITH ...后面跟着SELECT，因为它在找到FOR的地方。
我没有在plpgsql的查询中使用过WITH子句，但我尝试将FOR cat IN移到WITH之前，这是查询的一部分。
Stack Overflow用户
发布于 2010-05-15 17:21:38
试试这个：
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint) RETURNS int AS
$$
DECLARE catNumber int DEFAULT 0;
DECLARE cat RECORD;
BEGIN
FOR cat IN
WITH RECURSIVE children(categoryid,category_fk) AS (
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
SELECT * FROM children
LOOP
IF EXISTS (SELECT 1 FROM b2m.repoobject_tab WHERE category_fk = cat.categoryid) THEN
catNumber = catNumber +1
END IF;
END LOOP;
RETURN catNumber;
END;
$$ LANGUAGE 'plpgsql';我注意到您的查询几乎不需要循环，只需这样做：
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint) RETURNS int AS
$$
BEGIN
RETURN
(
SELECT COUNT(*) FROM b2m.repoobject_tab WHERE category_fk IN
(
WITH RECURSIVE children(categoryid,category_fk) AS
(
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
SELECT categoryid FROM children
)
);
END;
$$ LANGUAGE 'plpgsql';页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2839479
复制相关文章
相似问题
腾讯云开发者
