From: Gnanavel Shanmugam <s.gnanavel@inbox.com>
To: pgsql-sql@postgresql.org
Subject: Re: Recursive function
Date: Mon, 4 Jul 2005 21:32:38 -0800
Message-ID: <EF708ED44A0.00000543s.gnanavel@inbox.com> (raw)
In-Reply-To: <EF33BD7FFF8.00000518s.gnanavel@inbox.com>
solved the problem. The modified function is
CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int
AS $$
declare
v_section_type_id alias for $1;
v_rec record;
v_rec1 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;
for v_rec1 in select * from
get_child_section_types(v_rec.section_type_id) loop
return next v_rec1.get_child_section_types;
end loop;
end loop;
return;
end;
$$
LANGUAGE plpgsql;
with regards,
S.Gnanavel
> -----Original Message-----
> From: s.gnanavel@inbox.com
> Sent: Mon, 4 Jul 2005 21:05:25 -0800
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Recursive function
>
> 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
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: <EF708ED44A0.00000543s.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