X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F8825287F for ; Tue, 5 Jul 2005 02:05:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93991-07 for ; Tue, 5 Jul 2005 05:05:28 +0000 (GMT) Received: from WM12.inbox.com (wm12.inbox.com [208.50.6.12]) by svr1.postgresql.org (Postfix) with SMTP id 1F40852815 for ; Tue, 5 Jul 2005 02:05:26 -0300 (ADT) Received: from inbox.com (127.0.0.1:25) by inbox.com with [InBox.Com ESMTP Server] id <507040051562.WM12> for from ; Mon, 4 Jul 2005 9:05:27 PM -0800 Mime-Version: 1.0 Date: Mon, 4 Jul 2005 21:05:25 -0800 Message-ID: From: Gnanavel Shanmugam Subject: Recursive function To: pgsql-sql@postgresql.org X-Mailer: IBISIT WebMail Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.058 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/19 X-Sequence-Number: 21997 Hi, I have a table with the following details. section_type_id =7C section_type_name =7C parent_section_type_id -----------------+-------------------+------------------------ 10 =7C Unit =7C 20 =7C Block =7C 10 30 =7C Practice =7C 20 40 =7C Sub Practice =7C 30 I've written a function as this CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int AS =24=24 =09=09declare =09=09=09v_section_type_id alias for =241; =09=09=09v_rec record; begin =09=09=09for v_rec in select section_type_id from master_section_type where parent_section_type_id=3Dv_section_type_id loop =09=09=09=09return next v_rec.section_type_id; =09=09=09end loop; =09=09=09return; end; =24=24 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=