Большая иерархия в SQL запросе + PostgreSQL

статей часть 1 и часть 2, в которых предложено решение задачи визуализации иерархической структуры или ее части средствами SQL запросов на примере MySQL и SQLite

with recursive  Mapping as ( select  id as node_id,  parent_directory_id as parent_node_id, name as node_name from Files ),  RootNodes as ( select node_id as root_node_id from Mapping where -- Exactly one line below should be uncommented -- parent_node_id is null-- Uncomment to build from root(s) node_id in (3, 10, 17)-- Uncomment to add node_id(s) into the brackets ),  Levels as ( select node_id, parent_node_id, node_name, cast(parent_node_id as varchar) as parents, cast(node_name as varchar) as full_path, 0 as node_level from Mapping inner join RootNodes on node_id = root_node_id  union  select  Mapping.node_id,  Mapping.parent_node_id, Mapping.node_name, concat(coalesce(concat(prev.parents, '-'), ''), cast(Mapping.parent_node_id as varchar)), concat_ws(' ', prev.full_path, Mapping.node_name), prev.node_level + 1 from  Levels as prev inner join Mapping on Mapping.parent_node_id = prev.node_id ),  Branches as ( select node_id, parent_node_id, node_name, parents, full_path, node_level, case when root_node_id is null then case when node_id = last_value(node_id) over WindowByParents then '└── ' else '├── ' end else '' end as node_branch, case when root_node_id is null then case when node_id = last_value(node_id) over WindowByParents then '    ' else '│   ' end else '' end as branch_through from Levels left join RootNodes on node_id = root_node_id window WindowByParents as ( partition by parents order by node_name rows between current row and unbounded following ) order by full_path ),  Tree as ( select node_id, parent_node_id, node_name, parents, full_path, node_level, node_branch, cast(branch_through as varchar) as all_through from Branches inner join RootNodes on node_id = root_node_id  union  select  Branches.node_id, Branches.parent_node_id, Branches.node_name, Branches.parents, Branches.full_path, Branches.node_level, Branches.node_branch, concat(prev.all_through, Branches.branch_through) from  Tree as prev inner join Branches on Branches.parent_node_id = prev.node_id ),  FineTree as ( select tr.node_id, tr.parent_node_id, tr.node_name, tr.parents, tr.full_path, tr.node_level, concat(coalesce(parent.all_through, ''), tr.node_branch, tr.node_name) as fine_tree from Tree as tr left join Tree as parent on parent.node_id = tr.parent_node_id order by tr.full_path )  select fine_tree, node_id from FineTree ;

# # Generate SQL-script for creation of hierarchical table by zip-archive structure # from zipfile import ZipFile from itertools import count   def file_size_b(file_size):     """ Returns file size string in XBytes """     for b in ["B", "KB", "MB", "GB", "TB"]:         if file_size < 1024:             break         file_size /= 1024     return f"{round(file_size)} {b}"   def gen_table(zip_file, table_name='ZipArchive', chunk_size=10000, out_extension='sql'):     """     by iqu 2024-04-28     params:         zip_file - zip archive full path         table_name - table to be created         chunk_size - limit values() for each insert into part         out extension - replaces zip archive file extension     ->  None, creates file with SQL script.         Create table columns:         id, name, parent_id, file_size - obvious,         bytes - string with file size generated by file_size_b()     """     def gen_create_table(file):         print(f"drop table if exists {table_name};", file=file)         print(f"create table {table_name} (id int, name varchar(255), parent_id int, file_size int, bytes varchar(16))"               , file=file)      def gen_insert(file):         print(f";\ninsert into {table_name} (id, name, parent_id, file_size, bytes) values", file=file)      out_file = ".".join(zip_file.split(".")[:-1] + [out_extension])      cnt = count()     parents = ['NULL']      with open(out_file, mode='w') as of:         gen_create_table(of)          with ZipFile(zip_file) as zf:             for zi in zf.infolist():                 zi_id = cnt.__next__()                 if zi_id % chunk_size == 0:                     gen_insert(of)                     delimiter = ''                 else:                     delimiter = ','                  level = zi.filename.count("/") - zi.is_dir()                 name = zi.filename.split("/")[level]                 file_size = -1 if zi.is_dir() else zi.file_size                 file_size_s = 'DIR' if zi.is_dir() else file_size_b(zi.file_size)                 if zi.is_dir():                     if len(parents) < level + 2:                         parents.append(f"{zi_id}")                     else:                         parents[level + 1] = f"{zi_id}"                  print(f"{delimiter}({zi_id}, '{name}', {parents[level]}, {file_size}, '{file_size_s}')", file=of)             print(';', file=of)   gen_table(r"C:\TEMP\jdk-master.zip")    # Sample archive https://github.com/openjdk/jdk/archive/refs/heads/master.zip 

Объяснять код в деталях не буду, это совсем не по теме статьи. Существенно, что скрипт разбивается на части длиной максимум chunk_size (10 000 по-умолчанию) в каждом блоке values()

Кроме ИД, имени и ссылки на родителя, каждая запись содержит поле file_size с длиной в байтах (-1 для папок) и поле bytes с длиной, преобразованной к строке в байтах, килобайтах и т.д. ('DIR' для папок)