From: "Nick Stone" <nick@harelane.com>
To: "'Gnanavel Shanmugam'" <s.gnanavel@inbox.com>, <pgsql-sql@postgresql.org>
Subject: Re: Recursive function
Date: Tue, 5 Jul 2005 07:27:06 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <EF33BD7FFF8.00000518s.gnanavel@inbox.com>
Thread-Index: AcWBIDeAaFQiMVuEQ3G+Nl0xk2cxLQACinpQ
Message-Id: <20050705062903.8BB0C24DEF9@smtp.nildram.co.uk>

Hi,

Also you could take a look at the connectby function as this will do what
you want with ease and it can be used to sort stuff at the same time,
produce tree views of data etc.

Nick 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Gnanavel Shanmugam
Sent: 05 July 2005 06:05
To: pgsql-sql@postgresql.org
Subject: [SQL] Recursive function

Hi,

 I have a table with the following details.

 section_type_id | section_type_name | parent_section_type_id
-----------------+-------------------+------------------------
              10 | Unit              |
              20 | Block             |                     10
              30 | Practice          |                     20
              40 | Sub Practice      |                     30

I've written a function as this

CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int
    AS $$
		declare
			v_section_type_id alias for $1;
			v_rec record;
        begin
			for v_rec in select section_type_id from
master_section_type where parent_section_type_id=v_section_type_id loop
				return next v_rec.section_type_id;
			end loop;
			return;
        end;
$$
    LANGUAGE plpgsql;

which returns output like,

select * from get_child_section_types(10);  get_child_section_types
-------------------------
                      20

but I need the function to return all section types under the child nodes
too.
So, how to make the function to be recursive.

with thanks & regards,
S.Gnanavel
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match