example of recursive select in postgres

postgre-recursive.sql

--- in database of ФИАС get address records recursively until root record

WITH RECURSIVE parent AS
 ( SELECT a.*
   FROM addrobj a
   WHERE a.aoguid = @locality
     AND a.actstatus = 1
   UNION ALL
   SELECT ao.*
   FROM parent p
   JOIN addrobj ao ON p.parentguid = ao.aoguid
   WHERE p.aoguid IS NOT NULL
     AND ao.actstatus = 1) 
SELECT * 
FROM parent

postgre-window.sql

--- group by update date and get latest set of addresses
WITH addrobj_noduplicates AS " +
(
       SELECT *, ROW_NUMBER() OVER (PARTITION BY fa.aoguid ORDER BY fa.startdate DESC) as rn 
       FROM fias.addrobj fa 
       WHERE 
           fa.aolevel = ANY (:aolevel)
           AND fa.actstatus=1
           AND fa.formalname ~* @region
       ORDER BY fa.aolevel, fa.startdate DESC
       LIMIT @limit
) 
SELECT *
    FROM addrobj_noduplicates fa 
WHERE
   fa.rn = 1