Recursive function

From: Gnanavel Shanmugam <s(dot)gnanavel(at)inbox(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Recursive function
Date: 2005-07-05 05:05:25
Message-ID: EF33BD7FFF8.00000518s.gnanavel@inbox.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gnanavel Shanmugam 2005-07-05 05:32:38 Re: Recursive function
Previous Message Din Adrian 2005-07-04 23:10:03 Re: Help on Procedure running external function