sql - Tree Structure and Recursion -

Using a PostgreSQL 8.4.14 database, I have an example such as representing a tree structure:

create table unit (id bilton null primary key, name varchar (64) tap, parent_id big, foreign key (parent_ID) reference unit (id)); INSERT (1, 'parent', NULL) in unit value, (2, 'child', 1), (3, 'grandson, a', 2), (4, 'grandson, b', 2);

  id | Name | Parent_id ---- + -------------- + ----------- 1 | Parents 2 | Children 1 3 | Grandson a | 2 4 | I want to create an access control list for those units, where each unit has its own ACL, or it should be inherited from the nearest ancestor with its ACL.  
  create table ACL (Unit_ID bentunt faucet primary key, foreign key (unit_id) reference unit (ID)); Insert in ACL Value (1), (4);   
  unit_ id --------- 1 4   
 I am To determine whether a unit is receiving ACL from the predecessor, using:  
  create view as inheriting_acl u Id as unit_id is inheriting the unit in the form of COUNT (a. *) = 0, such as leaving u as an AL.l. on.unit_id = u.id GROUP BY u.id;   
  unit_id | Inheriting --------- + ------------ 1 | F2 T3 | T4 | F.   
  My question is:  How can I get the nearest unit which  does not  get an ACL from an ancestor? My estimated result should look like the following table / view:  
  unit_id | ACL --------- + ------------ 1 | 1 2 | 1 3 | 1 4 | 4    
 
 
 Job can have a query with PostgreSQL  8.4  Or later version is required:  
  as recommended next_in_line (select UID AS Unit_ID, UPERID, a.unit_id in the form of unit from the UK Skip I select a.unit_id = u.id unius at one n.unit_id, u.parent_id, a.unit_id next_in_line Join N. Unit u on u.id = n.parent_id and n.acl is tap left one Include at a. Unit_id = U.ID) Select unit unit_ID, ACL from next_in_line WHERE ACL Unit_ID does not have a null sequence   
 
 
 
 
 NACL's Null . Additionally,  acl  is found as soon as the query stops with the way of the tree. In the last  SELECT  we only return the rows where a  acl  was found in the form of VoilÃÆ'à  â € ™ Ã⠀ šÃ, · On one side: This is an anti-pattern to use as a normal, non-descriptive  ID  column name. Unfortunately, some ORM do this by default, because it  unit_id  Call and you do not need to use aliases in questions at all times.