solved the problem. The modified function is
CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int
AS $$
declare
v_section_type_id alias for $1;
v_rec record;
v_rec1 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;
for v_rec1 in select * from
get_child_section_types(v_rec.section_type_id) loop
return next v_rec1.get_child_section_types;
end loop;
end loop;
return;
end;
$$
LANGUAGE plpgsql;
with regards,
S.Gnanavel
> -----Original Message-----
> From: [EMAIL PROTECTED]
> Sent: Mon, 4 Jul 2005 21:05:25 -0800
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
