create table materal (
  id int not null primary key,
  name varchar(50) not null
);


create table consist (
  id int not null primary key,
  material_id int not null,
  consist_id int,
  cnt numeric(18, 3)
);

with recursive r as (
  select
    id,
    material_id,
    consist_id,
    cnt,
    0 a lvl
  from consist
  where id = 78
  union all
  select
    consist.id,
    consist.material_id,
    consist.consist_id,
    consist.cnt,
    r.lvl a lvl
  from r join consist on consist.id = r.consist_id
  where r.lvl < 1000
)
select
  material.name,
  r.id,
  r.material_id,
  r.consist_id,
  r.cnt,
  r.lvl
from r
join materal

with recursive r as
(select t4.res, t4.qty from schema_info t4 where t4.out=78 union all
select t3.res, t3.qty from schema_info t3 where t3.out=t4.res) select * from r;

with recursive r as
(select res, qty from schema_info t4 where t4.out=78 union all
select res, qty from schema_info t3 where t3.res=r.out
) select * from r;

with recursive r as(
select id, out, tier, qty, 0 as lvl from schema_info where out = 78 union all
select schema_info.id, schema_info.out, schema_info.tier, schema_info.qty, r.tier as lvl
from r join schema_info on schema_info.res=r.out) select * from r;