Problem with Postgres FOR LOOP
- by user341831
Hi all,
Ich 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;
RETURN catNumber;
END;
$$ LANGUAGE 'plpgsql';
I've got error:
FEHLER: Syntaxfehler bei »FOR«
LINE 1: ...dren WHERE children.categoryid = c1.category_fk ) FOR $2 I...
I'm a newbee in Postgres. Please help.
Thanx in advance