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 |
SubPractice | 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
aliasfor $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