pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRecursive function
3+ messages / 2 participants
[nested] [flat]
* Recursive function
@ 2005-07-05 05:05 Gnanavel Shanmugam <s.gnanavel@inbox.com>
0 siblings, 2 replies; 3+ messages in thread
From: Gnanavel Shanmugam @ 2005-07-05 05:05 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Recursive function
@ 2005-07-05 05:32 Gnanavel Shanmugam <s.gnanavel@inbox.com>
parent: Gnanavel Shanmugam <s.gnanavel@inbox.com>
1 sibling, 0 replies; 3+ messages in thread
From: Gnanavel Shanmugam @ 2005-07-05 05:32 UTC (permalink / raw)
To: 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.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Recursive function
@ 2005-07-05 06:27 Nick Stone <nick@harelane.com>
parent: Gnanavel Shanmugam <s.gnanavel@inbox.com>
1 sibling, 0 replies; 3+ messages in thread
From: Nick Stone @ 2005-07-05 06:27 UTC (permalink / raw)
To: 'Gnanavel Shanmugam' <s.gnanavel@inbox.com>; pgsql-sql
Hi,
Also you could take a look at the connectby function as this will do what
you want with ease and it can be used to sort stuff at the same time,
produce tree views of data etc.
Nick
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Gnanavel Shanmugam
Sent: 05 July 2005 06:05
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-05 06:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-05 05:05 Recursive function Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-07-05 05:32 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-07-05 06:27 ` Nick Stone <nick@harelane.com>
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