Home > mailing lists

SQL Query for Top Down fetching of childs - Mailing list pgsql-sql

From	Kumar
Subject	SQL Query for Top Down fetching of childs
Date	January 29, 2004 05:11:16
Msg-id	00ad01c3e62e$b70ccf60$7502a8c0@hdsc.com Whole thread Raw
Responses	Re: SQL Query for Top Down fetching of childs
List	pgsql-sql

Tree view

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 list all the departments(Childs) of a parent organization. The table structure is

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

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

Again, I need to query the entity_id 15 to get its child

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_entity_id FROM organization ben
WHERE ben.parent_entity_id = pi_entity_id

LOOP
  IF rec_result.entity_type = \'EngineeingTeam\' THEN
       RETURN NEXT rec_result;
  ELSE
       v_patent_entity_id := rec_result.entity_id;
   LOOP
      FOR rec_proc IN SELECT bse.entity_id, bse.entity_name, bse.entity_type, bse.parent_entity_id FROM organization bse
                                 WHERE bse.parent_entity_id= v_patent_entity_id

    LOOP
    IF rec_proc.entity_type = \'EngineeringTeam\' THEN
         RETURN NEXT rec_proc;
    ELSE
        v_patent_entity_id := rec_proc.entity_id;
   END IF;
   END LOOP;
   EXIT WHEN rec_proc.entity_type = \'EngineeringTeam\';
   END LOOP;
  END IF;
END LOOP;
RETURN;
END;'
LANGUAGE 'plpgsql' VOLATILE;

Anybody pls help me with this. I am first time writing these kind of function for TOP DOWN analysis. Please shed light.

Regards

Senthil Kumar S

pgsql-sql by date:

From: "Octavio Alvarez"
Date: 29 January 2004, 03:27:29
Subject: LEFT JOIN on one and/or another column

From: "jodi"
Date: 29 January 2004, 07:28:33
Subject: auto_insert

SQL Query for Top Down fetching of childs - Mailing list pgsql-sql

Previous

Next