Recursive PLPGSQL function?
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.
"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes:
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:
You've got an infinite recursion there --- the function is calling
itself back with the original argument value.
regards, tom lane
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:
Show quoted text
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
EnglandTel: +44 (0)1752 764445
Fax: +44 (0)1752 764446This 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)
-----Original Message-----
From: DeJuan Jackson [mailto:djackson@speedfc.com]
Sent: 04 August 2004 17:56
To: Mark Cave-Ayland
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.
Ahhh yes I got it now :) I was pulling in the wrong field from the inner
nested record set which was causing it to call itself as you suggested.
I did suspect this and put a RAISE NOTICE at the beginning of the
function to try and debug it, but the message was only displayed perhaps
once every several hundred lines of output between many "CONTEXT:
PL/pgSQL function "findsubcategories" line 15 at for over" messages,
which made me think that something was being overwritten in the
recursion (for an infinite recursion I would have expected the screen to
fill with hundreds of RAISE NOTICE message until something died).
Anyway, it all seems to be working now - thanks very much for the help.
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.
Import Notes
Reply to msg id not found: 8F4A22E017460A458DB7BBAB65CA6AE52905DC@openmanage | Resolved by subject fallback