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
aliasfor $1; v_rec record; v_rec1 record; begin for v_rec in select section_type_id from
master_section_typewhere
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;
endloop; end loop; return; end;
$$ LANGUAGE plpgsql;
with regards,
S.Gnanavel
> -----Original Message-----
> From: s.gnanavel@inbox.com
> 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
