CREATE TABLE REGIONS (
    ID          "INT" NOT NULL /* "INT" = INTEGER */,
    PARENT      "INT" NOT NULL /* "INT" = INTEGER */,
    NAME        STRING50 NOT NULL COLLATE PXW_CYRL /* STRING50 = VARCHAR(50) */,
    CCOUNT      "INT" DEFAULT 0 NOT NULL /* "INT" = INTEGER */,
    SORTINDEX   "INT" DEFAULT 0 NOT NULL /* "INT" = INTEGER */,
    IMAGEINDEX  "INT" DEFAULT -1 NOT NULL /* "INT" = INTEGER */,
    STATEINDEX  "INT" DEFAULT -1 NOT NULL /* "INT" = INTEGER */,
    FULLPATH    STRING255 NOT NULL COLLATE PXW_CYRL /* STRING255 = VARCHAR(255) */);

with recursive
        r as(
            select id, parent, name
            from regions
            where parent = 0

            union all

            select s.id, s.parent, r.name||'>'||s.name
            from regions s join r on s.parent=r.id
        )
        select name from r
        where r.id=187

select id, parent, name
            from regions
            where parent = 0

SET TERM ^ ;

create or alter procedure SP_GET_FULL_REGION (
    ID_REGION type of column REGIONS.ID)
as
declare variable RES varchar(1024) collate PXW_CYRL;
BEGIN
FOR SELECT NAME FROM
  (WITH RECURSIVE
        R AS(
            SELECT ID, PARENT, NAME
            FROM REGIONS
            WHERE PARENT = 0 OR (PARENT IS NULL)

            UNION ALL

            SELECT S.ID, S.PARENT, R.NAME||'>'||S.NAME
            FROM REGIONS S JOIN R ON S.PARENT=R.ID)
        SELECT SUBSTRING(NAME FROM 1 FOR 1024) NAME FROM R
        WHERE R.ID = :ID_REGION)

        INTO :RES
        DO


  SUSPEND;
END^

SET TERM ; ^

SELECT ID, PARENT, NAME
            FROM REGIONS
            WHERE PARENT = 0 and ID = 187

WITH RECURSIVE
        R AS(
            SELECT ID, PARENT, NAME
            FROM REGIONS
            WHERE PARENT = 0

            UNION ALL

            SELECT S.ID, S.PARENT, R.NAME||'>'||S.NAME
            FROM REGIONS S JOIN R ON S.PARENT=R.ID)
        SELECT SUBSTRING(NAME FROM 1 FOR 1024) NAME FROM R
        WHERE R.ID = 2780

with recursive r (code_horse, code_parent, name) as (
  select
    id, parent, name
  from regions
  where id=187
  union all
  select
    regions .id,
    regions.parent,
    regions.name
 from r join regions on regions.parent = r.id
 where r.parent > 0
)
select
  list(name, '>') as path
from r

with recursive r (code_horse, code_parent, name) as (
  select
    id, parent, name
  from regions
  where id=187
  union all
  select
    regions .id,
    regions.parent,
    regions.name
 from r join regions on regions.parent = r.code_horse
 where code_parent > 0
)
select
  list(name, '>') as path
from r

with recursive r (id, parent, name) as (
  select
    id, parent, name
  from regions
  where id=187
  union all
  select
    regions .id,
    regions.parent,
    regions.name
 from r join regions on regions.parent = r.id
 where r.parent > 0
)
select
  list(name, '>') as path
from r

BEGIN
    SELECT PARENT, NAME FROM TYPES WHERE ID = :ID INTO :AID, :APATH;

    WHILE (APATH IS NOT NULL) DO BEGIN
        IF (PATH IS NOT NULL) THEN
            PATH = APATH || '>' || PATH;
        ELSE
            PATH = APATH;
        IF (AID <> 0) THEN
            SELECT PARENT, NAME FROM TYPES WHERE ID = :AID INTO :AID, :APATH;
        ELSE
            APATH = NULL;
    END
    IF (:PATH IS NOT NULL) THEN SUSPEND;
END

CREATE PROCEDURE GET_PATH (ID INT)
RETURNS (
    PATH BLOB SUB_TYPE TEXT)
AS
BEGIN
  FOR
      WITH RECURSIVE R (
          ID,
          PARENT,
          NAME,
          DEPTH)
      AS (SELECT
              ID,
              PARENT,
              NAME,
              1
          FROM
              REGIONS
          WHERE ID = :ID
          UNION ALL
          SELECT
              REGIONS.ID,
              REGIONS.PARENT,
              REGIONS.NAME,
              R.DEPTH + 1
          FROM
              R
              JOIN REGIONS ON REGIONS.ID = R.PARENT
          WHERE R.PARENT > 0),
      T
      AS (SELECT
              ID,
              PARENT,
              NAME,
              DEPTH
          FROM
              R
          ORDER BY DEPTH DESC)
      SELECT
          LIST(NAME, '->') AS PATH
      FROM
          T
      INTO PATH
  DO
    SUSPEND;
END

IF (:ID_REGION IS NULL) THEN EXIT;