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