gistfile1.sql

CREATE VIEW area_parent AS
  SELECT entity0 AS parent, entity1 AS child FROM l_area_area WHERE link IN (
    SELECT id FROM link WHERE link_type IN (
      SELECT id FROM link_type WHERE gid = 'de7cc874-8b1b-3a05-8272-f3834c968fb7'
    )
  );

WITH
  RECURSIVE area_root (leaf, id, name, root) AS (
    SELECT area.id, area.id, area.name, area_parent.parent
    FROM area
    JOIN area_parent ON area.id = area_parent.child
    UNION ALL
    SELECT area_root.leaf, area.id, area.name, area_parent.parent
    FROM area_root
    JOIN area ON area_root.root = area.id
    LEFT JOIN area_parent ON area.id = area_parent.child
  )
SELECT *
FROM area_root;