AT OBJECT

CREATE TABLE at_object (
  id              dintkey,
  namespacekey    dintkey,
  objectname      dname,
  objectclass     dclassname NOT NULL,
  subtype         dtext60,
  xid             dinteger_notnull,
  dbid            dinteger_notnull,
  objectpos       dinteger,
  alwaysoverwrite dboolean_notnull DEFAULT 0,
  dontremove      dboolean_notnull DEFAULT 0,
  includesiblings dboolean_notnull DEFAULT 0,
  headobjectkey   dforeignkey,
  modified        TIMESTAMP,
  curr_modified   TIMESTAMP,
 
  CONSTRAINT at_pk_object PRIMARY KEY (id),
  CONSTRAINT at_fk_object_namespacekey FOREIGN KEY (namespacekey)
    REFERENCES at_namespace (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT at_fk_object_headobjectkey FOREIGN KEY (headobjectkey)
    REFERENCES at_object (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT at_chk_object_hk CHECK (headobjectkey IS DISTINCT FROM id)
);

WITH RECURSIVE
  group_tree AS (
    SELECT id, headobjectkey, objectname, id AS root_id,
      CAST('' AS VARCHAR(255)) AS indent
    FROM at_object
    WHERE headobjectkey IS NULL
 
    UNION ALL
 
    SELECT g.id, g.headobjectkey, g.objectname, h.root_id,
      h.indent || rpad('', 4)
    FROM at_object g JOIN group_tree h
    ON g.headobjectkey = h.id
)
SELECT
  gt.indent || gt.objectname
FROM
  group_tree gt
WHERE
  gt.root_id = :ID