Syntax error with Postgres FOR LOOP
I have a problem in postgres function:
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;
RETU开发者_C百科RN catNumber;
END;
$$ LANGUAGE 'plpgsql';
I've got error:
FEHLER: Syntax error near »FOR«
LINE 1: ...dren WHERE children.categoryid = c1.category_fk ) FOR $2 I...
The syntax error is because it is expecting the WITH ...
to be followed by SELECT
where it found FOR
.
I haven't used WITH
clauses in queries in plpgsql, but I'd try moving FOR cat IN
to before the WITH
, which is part of the query.
Try this:
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';
I noticed your query hardly needs looping, just do this:
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';
精彩评论