From: Gnanavel Shanmugam <s.gnanavel@inbox.com>
To: pgsql-sql@postgresql.org
Subject: Recursive function
Date: Mon, 4 Jul 2005 21:05:25 -0800
Message-ID: <EF33BD7FFF8.00000518s.gnanavel@inbox.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: s.gnanavel@inbox.com
Subject: Re: Recursive function
In-Reply-To: <EF33BD7FFF8.00000518s.gnanavel@inbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox