X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 263E852818
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 02:32:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 01586-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 05:32:39 +0000 (GMT)
Received: from WM12.inbox.com (wm12.inbox.com [208.50.6.12])
	by svr1.postgresql.org (Postfix) with SMTP id A169952806
	for <pgsql-sql@postgresql.org>; Tue,  5 Jul 2005 02:32:38 -0300 (ADT)
Received: from inbox.com (127.0.0.1:25)
	by inbox.com with [InBox.Com ESMTP Server]
	id <507040052529.WM12> for <pgsql-sql@postgresql.org> from
	<s.gnanavel@inbox.com>; Mon, 4 Jul 2005 9:32:39 PM -0800
Mime-Version: 1.0
Date: Mon, 4 Jul 2005 21:32:38 -0800
Message-ID: <EF708ED44A0.00000543s.gnanavel@inbox.com>
From: Gnanavel Shanmugam <s.gnanavel@inbox.com>
Subject: Re: Recursive function
To: pgsql-sql@postgresql.org
In-Reply-To: <EF33BD7FFF8.00000518s.gnanavel@inbox.com>
X-Mailer: IBISIT WebMail
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.054 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200507/20
X-Sequence-Number: 21998

solved the problem. The modified function is

CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int
    AS =24=24
=09=09declare
=09=09=09v_section_type_id alias for =241;
=09=09=09v_rec record;
=09=09=09v_rec1 record;
        begin
=09=09=09for v_rec in select section_type_id from master_section_type where
parent_section_type_id=3Dv_section_type_id loop
=09=09=09=09return next v_rec.section_type_id;
=09=09=09=09for v_rec1 in select * from
get_child_section_types(v_rec.section_type_id) loop
=09=09=09=09return next v_rec1.get_child_section_types;
=09=09=09=09end loop;
=09=09=09end loop;
=09=09=09return;
        end;
=24=24
    LANGUAGE plpgsql;

with regards,
S.Gnanavel


> -----Original Message-----
> From: s.gnanavel=40inbox.com
> Sent: Mon, 4 Jul 2005 21:05:25 -0800
> To: pgsql-sql=40postgresql.org
> Subject: =5BSQL=5D Recursive function
>
> Hi,
>
>  I have a table with the following details.
>
>  section_type_id =7C section_type_name =7C parent_section_type_id
> -----------------+-------------------+------------------------
>               10 =7C Unit              =7C
>               20 =7C Block             =7C                     10
>               30 =7C Practice          =7C                     20
>               40 =7C Sub Practice      =7C                     30
>
> I've written a function as this
>
> CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int
>     AS =24=24
> =09=09declare
> =09=09=09v_section_type_id alias for =241;
> =09=09=09v_rec record;
>         begin
> =09=09=09for v_rec in select section_type_id from master_section_type =
where
> parent_section_type_id=3Dv_section_type_id loop
> =09=09=09=09return next v_rec.section_type_id;
> =09=09=09end loop;
> =09=09=09return;
>         end;
> =24=24
>     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=