Re: Recursive PLPGSQL function?

If I'm not mistaken you have an infinit recursion because you are always
pulling the same id (whatever _id starts at) throughout each function call.

Postgres is most likely killing the functions when it's hits some stack
or memory limit.

Mark Cave-Ayland wrote:

>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.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>