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
