From: | Gnanavel Shanmugam <s(dot)gnanavel(at)inbox(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Recursive function |
Date: | 2005-07-05 05:32:38 |
Message-ID: | EF708ED44A0.00000543s.gnanavel@inbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)gnanavel(at)inbox(dot)com
> Sent: Mon, 4 Jul 2005 21:05:25 -0800
> To: pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Stone | 2005-07-05 06:27:06 | Re: Recursive function |
Previous Message | Gnanavel Shanmugam | 2005-07-05 05:05:25 | Recursive function |