Функция поиска заданной улицы внутри Москвы и Московской области

gistfile1.sql

CREATE OR REPLACE FUNCTION search_street(IN street text)
  RETURNS TABLE(aoguid uuid, fulladdress text, postalcode integer) AS
$BODY$
  WITH RECURSIVE child_to_parents AS (
  SELECT aoguid, format('{"%s": %s}', fias_addr_obj.shortname, formalname) AS fulladdress,
           postalcode, parentguid, aolevel
      FROM fias_addr_obj
      WHERE currstatus = 0 
      AND (regioncode = '50' OR regioncode = '77') 
      AND formalname ILIKE '%' || format('%s', street) || '%'
      AND parentguid IS NOT NULL
  UNION ALL
  SELECT child_to_parents.aoguid, format('{"%s":%s, %s}', fias_addr_obj.shortname, fias_addr_obj.formalname, child_to_parents.fulladdress) AS fulladdress,
           CASE
             WHEN child_to_parents.postalcode IS NULL THEN fias_addr_obj.postalcode
             ELSE child_to_parents.postalcode
           END AS postalcode,
           fias_addr_obj.parentguid, fias_addr_obj.aolevel
  FROM fias_addr_obj
  INNER JOIN child_to_parents ON (fias_addr_obj.aoguid = child_to_parents.parentguid)
  WHERE fias_addr_obj.aoguid = child_to_parents.parentguid 
  AND fias_addr_obj.currstatus = 0 
  AND (fias_addr_obj.regioncode = '50' OR fias_addr_obj.regioncode = '77') 
)
SELECT aoguid, fulladdress, postalcode FROM child_to_parents WHERE aolevel = 1
GROUP BY aoguid, fulladdress, postalcode
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION search_street(text)
  OWNER TO postgres;