example

-- Final Combined Query: List Owned Entities with Owner and Details

WITH RECURSIVE
  InputParams AS (
    SELECT
      'group:default/org-example' AS start_ref,
      'Group' AS start_kind
  ),
  GroupHierarchy (owner_ref) AS (
    SELECT ip.start_ref FROM InputParams ip WHERE ip.start_kind = 'Group'
    UNION ALL
    SELECT r.target_entity_ref
    FROM public.relations r JOIN GroupHierarchy gh ON r.source_entity_ref = gh.owner_ref
    WHERE r.type = 'parentOf'
  ),
  UserMembership (owner_ref) AS (
    SELECT ip.start_ref FROM InputParams ip WHERE ip.start_kind = 'User'
    UNION
    SELECT r.target_entity_ref
    FROM public.relations r JOIN InputParams ip ON r.source_entity_ref = ip.start_ref
    WHERE ip.start_kind = 'User' AND r.type = 'memberOf' AND r.target_entity_ref LIKE 'group:%'
  ),
  FinalOwners (owner_ref) AS (
    SELECT gh.owner_ref FROM GroupHierarchy gh JOIN InputParams ip ON TRUE WHERE ip.start_kind = 'Group'
    UNION
    SELECT um.owner_ref FROM UserMembership um JOIN InputParams ip ON TRUE WHERE ip.start_kind = 'User'
    UNION
    SELECT ip.start_ref FROM InputParams ip WHERE ip.start_kind NOT IN ('Group', 'User')
  ),
  FilterKinds (kind) AS (
    VALUES ('Component'), ('API'), ('System')
  )
SELECT
  fe.entity_ref,
  r.target_entity_ref AS owner_entity_ref,
  ((fe.final_entity::json -> 'metadata') ->> 'description') AS description,
  (fe.final_entity::json ->> 'kind') AS kind,
  ((fe.final_entity::json -> 'spec') ->> 'type') AS component_type
FROM
  public.final_entities fe
JOIN
  public.relations r ON fe.entity_ref = r.source_entity_ref
WHERE
  fe.final_entity IS NOT NULL
  AND r.type = 'ownedBy'
  AND r.target_entity_ref IN (SELECT owner_ref FROM FinalOwners)
  AND (fe.final_entity::json ->> 'kind') IN (SELECT kind FROM FilterKinds);

gistfile1.txt

-- SQL Chunk 1: Identify the Set of Relevant Owners
-- Purpose: Find all owner entity_refs based on the starting entity and aggregation rules.

WITH RECURSIVE
  InputParams AS (
    SELECT
      -- === INPUT: Replace with the entity ref to start from ===
      :start_entity_ref AS start_ref,
      -- === INPUT: Replace with the kind ('Group', 'User', etc.) of the start_entity_ref ===
      :input_entity_kind AS start_kind
  ),
  GroupHierarchy (owner_ref) AS (
    SELECT ip.start_ref FROM InputParams ip WHERE ip.start_kind = 'Group'
    UNION ALL
    SELECT r.target_entity_ref
    FROM public.relations r JOIN GroupHierarchy gh ON r.source_entity_ref = gh.owner_ref
    WHERE r.type = 'parentOf'
  ),
  UserMembership (owner_ref) AS (
    SELECT ip.start_ref FROM InputParams ip WHERE ip.start_kind = 'User'
    UNION
    SELECT r.target_entity_ref
    FROM public.relations r JOIN InputParams ip ON r.source_entity_ref = ip.start_ref
    WHERE ip.start_kind = 'User' AND r.type = 'memberOf' AND r.target_entity_ref LIKE 'group:%'
  ),
  FinalOwners (owner_ref) AS (
    SELECT gh.owner_ref FROM GroupHierarchy gh JOIN InputParams ip ON TRUE WHERE ip.start_kind = 'Group'
    UNION
    SELECT um.owner_ref FROM UserMembership um JOIN InputParams ip ON TRUE WHERE ip.start_kind = 'User'
    UNION
    SELECT ip.start_ref FROM InputParams ip WHERE ip.start_kind NOT IN ('Group', 'User')
  )
-- Final Output of Chunk 1: Select the list of owner refs
SELECT owner_ref
FROM FinalOwners;