473,890 Members | 1,935 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 3824
"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
6336
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
3986
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
2483
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
2953
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
5861
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 PL/pgSQL function
1
4668
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
1869
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. Any insight would be *much* appreciated. Thanks! - Dave
9
10947
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
2218
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
9976
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9814
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11214
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10801
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10903
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10449
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8006
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5834
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6034
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.