[SQL] Recursive function

[Gnanavel Shanmugam]

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