services/api/lib/create_ancestor_view.sql

   1 CREATE TEMPORARY VIEW ancestor_view AS
   2 WITH RECURSIVE
   3 ancestor (uuid, ancestor_uuid) AS (
   4      SELECT groups.uuid::varchar(32)       AS uuid,
   5             groups.owner_uuid::varchar(32) AS ancestor_uuid
   6             FROM groups
   7      UNION
   8      SELECT ancestor.uuid::varchar(32)     AS uuid,
   9             groups.owner_uuid::varchar(32) AS ancestor_uuid
  10             FROM ancestor
  11             INNER JOIN groups
  12             ON groups.uuid = ancestor.ancestor_uuid
  13 )
  14 SELECT * FROM ancestor;