Recursive PLPGSQL function?
От | Mark Cave-Ayland |
---|---|
Тема | Recursive PLPGSQL function? |
Дата | |
Msg-id | 8F4A22E017460A458DB7BBAB65CA6AE5026627@openmanage обсуждение исходный текст |
Ответы |
Re: Recursive PLPGSQL function?
Re: Recursive PLPGSQL function? |
Список | pgsql-general |
Hi everyone, I'm trying to write a recursive plpgsql function in PostgreSQL 7.4.2 that given a tree node id (ictid) will return all the nodes below it in the tree, one row per node. When I try and execute the function I get the following error message: CONTEXT: PL/pgSQL function "findsubcategories" line 15 at for over select rows PL/pgSQL function "findsubcategories" line 15 at for over select rows PL/pgSQL function "findsubcategories" line 15 at for over select rows PL/pgSQL function "findsubcategories" line 15 at for over select rows ...repeated many many times... Can anyone see where I am going wrong in my function? I found a reference to "for over select rows" in pl_funcs.c but it appears to be denoting a statement type? The code is given below: CREATE OR REPLACE FUNCTION plpgsql.findsubcategories(int8) RETURNS SETOF inventory.cattree AS ' DECLARE _row inventory.cattree%ROWTYPE; _nrow inventory.cattree%ROWTYPE; _id ALIAS FOR $1; BEGIN -- Select the starting tree entry FOR _row IN SELECT * FROM inventory.cattree WHERE parentictid = _id LOOP -- Return this category RETURN NEXT _row; -- Recurse for each child function FOR _nrow IN SELECT * FROM plpgsql.findsubcategories(_row.parentictid) LOOP RETURN NEXT _nrow; END LOOP; END LOOP; -- Return the entire set RETURN; END ' LANGUAGE 'plpgsql'; If this is not possible, can anyone else suggest a way of getting the required result? Many thanks, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
В списке pgsql-general по дате отправления: