Recursion working in 7.3?

From: jeff_patterson(at)agilent(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Recursion working in 7.3?
Date: 2003-03-05 19:11:35
Message-ID: 08E9E30FCA2CD5119BEA0090274066DF09EDF4CB@axcs16.cos.agilent.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

As I understand it, recursion is supposed to be working in 7.3 but my first attempt is overflowing the stack and crashing the server. I have implemented a visitation model of a tree where subordinates share a common ancestral node (top in the table below). In this way, changes can be made to the tree without having to change the visitation numbers of every node in the table, only those who share a common top node.

I want to write a recursive routine which returns the level of a given node. Here's the code:

create function level (INTEGER,INTEGER)
RETURNS INTEGER AS'
DECLARE
post ALIAS FOR $1;
parent ALIAS for $2;
mygd INTEGER; --my grandfather
mydad INTEGER;
BEGIN
IF parent = 0 THEN
RETURN 1;
ELSE
select post,parent into mydad,mygd from threads where threads.post=parent; --get parent's info
RETURN level(mydad,mygd)+1; --my level is my parent's level +1
END IF;
END;
' LANGUAGE 'plpgsql';

Note that post is a primary key so the SELECT is guaranteed to return exactly one row.

Since every sub-tree contains a sub-root (in this case 153549) whose parent is by definition 0, the recursion should always terminate. It doesn't. Running the code on any node but the top node of the table below crashes the server.

mpip_bb=# select post,parent,top,lv,rv from threads where top = 153549;

post | parent | top | lv | rv
--------+--------+--------+----+----
153549 | 0 | 153549 | 1 | 12
153555 | 153549 | 153549 | 2 | 3
153568 | 153549 | 153549 | 4 | 5
153685 | 153549 | 153549 | 6 | 11
153693 | 153685 | 153549 | 7 | 10
153846 | 153693 | 153549 | 8 | 9
(6 rows)

Did I miss something?

Thanks,
Jeff Patterson
The Melanoma Patients' Information Page
http://www.mpip.org

Attachment Content-Type Size
Jeff Patterson (E-mail).vcf application/octet-stream 559 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carlos Moreno 2003-03-05 19:22:19 Re: Why PostgreSQL?
Previous Message Carlos Moreno 2003-03-05 19:07:27 Re: Is It possible to fixe a table in mermory ?