X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7B9B6D1CACD for ; Thu, 29 Jan 2004 06:11:15 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 70052-01 for ; Thu, 29 Jan 2004 02:10:50 -0400 (AST) Received: from smtp103.mail.sc5.yahoo.com (smtp103.mail.sc5.yahoo.com [66.163.169.222]) by svr1.postgresql.org (Postfix) with SMTP id 0466DD1C915 for ; Thu, 29 Jan 2004 02:10:35 -0400 (AST) Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp103.mail.sc5.yahoo.com with SMTP; 29 Jan 2004 06:10:38 -0000 Message-ID: <00ad01c3e62e$b70ccf60$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "psql" Subject: SQL Query for Top Down fetching of childs Date: Thu, 29 Jan 2004 11:41:18 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00AA_01C3E65C.CF55EB00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_30_40 X-Spam-Level: X-Archive-Number: 200401/245 X-Sequence-Number: 16638 This is a multi-part message in MIME format. ------=_NextPart_000_00AA_01C3E65C.CF55EB00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Friends, Postgres 7.3.4 on RH Linux 7.2. I need a query to get the Childs of a parent (Top down analysis). Need to l= ist all the departments(Childs) of a parent organization. The table structu= re is=20 CREATE TABLE organization ( entity_id int4, entity_name varchar(100), entity_type varchar(25), parent_entity_id int4, ) WITH OIDS; A parent can have n number of Childs. So I need to list all the childs for = a parent. For example I query the Division , then it lists it Childs # select * from organization where parent_entity_id =3D 3; entity_id | entity_name | entity_type | parent_entity_id -----------+-------------+-----------------+------------------ 5 | HR | EngineeringTeam | 3 12 | PM | EngineeringTeam | 3 8 | Finance | Dept | 3 6 | Quality | Dept | 3 I need to drill down to the last level Engineering Team in this example. So I query entity_id 8 further, it gives me its Childs =3D# select * from organization where parent_entity_id =3D 8; entity_id | entity_name | entity_type | parent_entity_id -----------+-------------+-------------+------------------ 15 | Audit | Group | 8 16 | Mkt | Group | 8 (2 rows) Again, I need to query the entity_id 15 to get its child =3D# select * from organization where parent_entity_id =3D 15; entity_id | entity_name | entity_type | parent_entity_id -----------+-------------+-----------------+------------------ 17 | CA | EngineeringTeam | 15 18 | Comm | EngineeringTeam | 15 19 | EComm | EngineeringTeam | 15 (3 rows) I have used the following query, but not useful. CREATE OR REPLACE FUNCTION.fn_get_all_organization(int4) RETURNS SETOF organization AS 'DECLARE pi_entity_id ALIAS FOR $1; rec_result organization%ROWTYPE; rec_proc organization%ROWTYPE; v_patent_entity_id INT; BEGIN FOR rec_result IN SELECT entity_id, entity_name, entity_type, parent_entit= y_id FROM organization ben WHERE ben.parent_entity_id =3D pi_entity_id LOOP IF rec_result.entity_type =3D \'EngineeingTeam\' THEN RETURN NEXT rec_result; ELSE v_patent_entity_id :=3D rec_result.entity_id;=20 LOOP FOR rec_proc IN SELECT bse.entity_id, bse.entity_name, bse.entity_ty= pe, bse.parent_entity_id FROM organization bse WHERE bse.parent_entity_id=3D v_patent_ent= ity_id LOOP IF rec_proc.entity_type =3D \'EngineeringTeam\' THEN RETURN NEXT rec_proc; ELSE v_patent_entity_id :=3D rec_proc.entity_id; END IF; END LOOP; EXIT WHEN rec_proc.entity_type =3D \'EngineeringTeam\'; END LOOP; END IF;=20 END LOOP; RETURN; END;' LANGUAGE 'plpgsql' VOLATILE; Anybody pls help me with this. I am first time writing these kind of functi= on for TOP DOWN analysis. Please shed light. Regards Senthil Kumar S ------=_NextPart_000_00AA_01C3E65C.CF55EB00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Dear Friends,
 
Postgres 7.3.4 on RH Linux 7.2.
 
I need a query to get the Childs of a parent (Top down= =20 analysis). Need to list all the departments(Childs) of a parent organizatio= n.=20 The table structure is
 
CREATE TABLE organization
(
  entity_id=20 int4,
  entity_name varchar(100),
  entity_type=20 varchar(25),
  parent_entity_id int4,
) WITH OIDS;
 
A parent can have n number of Childs. So I need to list= all=20 the childs for a parent.
 
For example I query the Division , then it lists it=20 Childs
# select * from organization where parent_entity_id =3D= =20 3;
 entity_id | entity_name |   entity_type   |= =20 parent_entity_id
-----------+-------------+-----------------+-----------= -------
        =20 5 | HR          | EngineeringT= eam=20 |            &n= bsp;  =20 3
        12 |=20 PM          | EngineeringTeam= =20 |            &n= bsp;  =20 3
         8 |=20 Finance     |=20 Dept           =20 |            &n= bsp;  =20 3
         6 |=20 Quality     |=20 Dept           =20 |            &n= bsp;  =20 3
 
 
I need to drill down to the last level Engineering Team= in=20 this example.
So I query entity_id 8 further, it gives me its=20 Childs
=3D# select * from organization where parent_entity_id = =3D=20 8;
 entity_id | entity_name | entity_type |=20 parent_entity_id
-----------+-------------+-------------+---------------= ---
       =20 15 | Audit       |=20 Group      =20 |            &n= bsp;  =20 8
        16 |=20 Mkt         |=20 Group      =20 |            &n= bsp;  =20 8
(2 rows)
 
Again, I need to query the entity_id 15 to get its=20 child
=3D# select * from organization where parent_entity_id = =3D=20 15;
 entity_id | entity_name |   entity_type   = |=20 parent_entity_id
-----------+-------------+-----------------+-----------= -------
       =20 17 | CA          | Engineering= Team=20 |            &n= bsp; =20 15
        18 |=20 Comm        | EngineeringTeam=20 |            &n= bsp; =20 15
        19 |=20 EComm       | EngineeringTeam=20 |            &n= bsp; =20 15
(3 rows)
 
I have used the following query, but not useful.=
 
 
CREATE OR REPLACE=20 FUNCTION.fn_get_all_organization(int4)
  RETURNS SETOF organiz= ation=20 AS
'DECLARE
 pi_entity_id ALIAS FOR=20 $1;
 rec_result  organization%ROWTYPE;
 rec_proc&= nbsp; organization%ROWTYPE;
 v_patent_entity_id INT;
 
BEGIN
 FOR rec_result=20 IN SELECT entity_id, entity_name, entity_type, par= ent_entity_id FROM organization=20 ben
           =20            =20     WHERE ben.parent_entity_id =3D=20 pi_entity_id
 LOOP
  IF rec_result.entity_type =3D= =20 \'EngineeingTeam\' THEN
       RETURN NEXT= =20 rec_result;
  ELSE
   =20    v_patent_entity_id :=3D rec_result.entity_id;=20
   LOOP
       FOR rec_= proc=20 IN SELECT bse.entity_id, bse.entity_name, bse.entity_ty= pe, bse.parent_entity_id FROM organization=20 bse
           =20                = =20      WHERE bse.parent_entity_id=3D=20 v_patent_entity_id
    LOOP
    IF= =20 rec_proc.entity_type =3D \'EngineeringTeam\' THEN
   =20      RETURN NEXT=20 rec_proc;
    ELSE
     =   =20  v_patent_entity_id :=3D rec_proc.entity_id;
   END= =20 IF;
   END LOOP;
   EXIT WHEN=20 rec_proc.entity_type =3D \'EngineeringTeam\';
   END=20 LOOP;
  END IF; 
 END=20 LOOP;
RETURN;
END;'
  LANGUAGE 'plpgsql'=20 VOLATILE;
 
Anybody pls help me with this. I am first time writing = these=20 kind of function for TOP DOWN analysis. Please shed light.
 
Regards
Senthil Kumar S
 

------=_NextPart_000_00AA_01C3E65C.CF55EB00--