473,573 Members | 4,181 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 "findsubcategor ies" line 15 at for over
select rows
PL/pgSQL function "findsubcategor ies" line 15 at for over select rows
PL/pgSQL function "findsubcategor ies" line 15 at for over select rows
PL/pgSQL function "findsubcategor ies" 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.findsub categories(int8 ) RETURNS SETOF
inventory.cattr ee AS '
DECLARE
_row inventory.cattr ee%ROWTYPE;
_nrow inventory.cattr ee%ROWTYPE;
_id ALIAS FOR $1;

BEGIN
-- Select the starting tree entry
FOR _row IN SELECT * FROM inventory.cattr ee WHERE parentictid =
_id LOOP

-- Return this category
RETURN NEXT _row;

-- Recurse for each child function
FOR _nrow IN SELECT * FROM
plpgsql.findsub categories(_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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #1
2 3807
"Mark Cave-Ayland" <m.***********@ 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
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 "findsubcategor ies" line 15 at for over
select rows
PL/pgSQL function "findsubcategor ies" line 15 at for over select rows
PL/pgSQL function "findsubcategor ies" line 15 at for over select rows
PL/pgSQL function "findsubcategor ies" 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.findsub categories(int8 ) RETURNS SETOF
inventory.catt ree AS '
DECLARE
_row inventory.cattr ee%ROWTYPE;
_nrow inventory.cattr ee%ROWTYPE;
_id ALIAS FOR $1;

BEGIN
-- Select the starting tree entry
FOR _row IN SELECT * FROM inventory.cattr ee WHERE parentictid =
_id LOOP

-- Return this category
RETURN NEXT _row;

-- Recurse for each child function
FOR _nrow IN SELECT * FROM
plpgsql.findsu bcategories(_ro w.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 YourEmailAddres sHere" to ma*******@postg resql.org)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
6315
by: Petr Bravenec | last post by:
I have found that when I use the RETURN NEXT command in recursive function, not all records are returned. The only records I can obtain from function are records from the highest level of recursion. Does exist some work-around? Thanks Petr Bravenec example: create table foo (
6
3960
by: Martin Marques | last post by:
We are trying to make some things work with plpgsql. The problem is that I built several functions that call one another, and I thought that the way of calling it was just making the assign: var:=func1(arg1,arg2); which gave me an error near ")". Now if I did the same, but like this:
1
2458
by: Rajesh Kumar Mallah | last post by:
Hi, profile_row profile_master%ROWTYPE; in a plpgsql function gives the error below tradein_clients=# SELECT general.create_accounts(); WARNING: plpgsql: ERROR during compile of create_accounts near line 8
10
2924
by: lnd | last post by:
After copied pg database from one PC to another -I could not find plpgsql function(s) in the copied database. -had to instal plpgsql language handler again -whilst tables and data moved fine The copy included all under /cygwin/usr/local/pgsql/data and database was down while making a copy.
14
5776
by: Karl O. Pinc | last post by:
Hi, Thought perhaps some other eyes than mine can tell if I'm doing something wrong here or if there's a bug somewhere. I've never passed a ROWTYPE varaible to a function but I don't see where the problem is. I keep getting errors like (the first is my debug output): NOTICE: last cycle is: 11 WARNING: Error occurred while executing...
1
4645
by: Thomas Schoen | last post by:
Hi, is it possible to use a parameter of a plpgsql-function to order a selection inside the function? What i would like to do is pass a column-name/alias-name to a plpgsql function and use the variable as order-by-parameter like this: create function foo(varchar) RETURNS SETOF test AS '
2
1838
by: David Boone | last post by:
I've been trying to create functions with postgres, but it seems that queries run within a function take wayyy too long to complete. The increased time seems to be in the actual queries, not function call overhead or something, but I can't for the life of me figure out why it's slower like this. I've simplified it to what you see below. ...
9
10856
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make that work either. Anyway, what's wrong with this?) Version is: $ rpm -q postgresql
1
2190
by: Karl O. Pinc | last post by:
FYI, mostly. But I do have questions as to how to write code that will continue to work in subsequent postgresql versions. See code below. begintest() uses EXIT to exit a BEGIN block from within nested loops. No problem. begintest2() simplifies this, omitting the nested loops. Still no problem.
0
7789
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7707
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8215
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6432
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5605
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3743
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1325
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.