需要 PostgreSQL 查询方面的帮助，以便从指定架构中检索分层数据

问：

请考虑以下 PostgreSQL 数据库架构：

CREATE TABLE manufacturing_process (
    id              bigserial PRIMARY KEY,
    process_type    manufacturing_process_type NOT NULL,
    name            VARCHAR(255) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE manufacturing_process_group (
    record_id       bigserial UNIQUE,
    group_id        bigint NOT NULL,
    title           VARCHAR(255) NOT NULL,
    parent_id       bigint,
    child_ids       bigint[],
    expansion_type  manufacturing_process_expansion_type NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES manufacturing_process(id)
);

以下是上述表的示例数据集：

manufacturing_process （mp）：

+----+---------------+------+
| id | process_type  | name |
+----+---------------+------+
|  1 | manufacturing | m1   |
|  2 | manufacturing | m2   |
|  3 | manufacturing | m3   |
|  4 | manufacturing | m4   |
|  5 | manufacturing | m5   |
|  6 | manufacturing | m6   |
|  7 | packaging     | m7   |
|  8 | manufacturing | m8   |
|  9 | manufacturing | m9   |
| 10 | manufacturing | m10  |
| 11 | manufacturing | m11  |
| 13 | packaging     | m13  |
+----+---------------+------+

manufacturing_process_group （mpg）：

+----+----------+------------------+-----------+-----------+----------------+
| id | group_id |      title       | parent_id | child_ids | expansion_type |
+----+----------+------------------+-----------+-----------+----------------+
|  1 |        1 | Group 1          | NULL      | {2,3,4}   | checkbox       |
|  2 |        1 | Subgroup 1.1     | 4         | {5,6}     | radio          |
|  3 |        1 | Subgroup 1.1.1   | 5         | {8,9}     | radio          |
|  4 |        1 | Subgroup 1.1.1.1 | 8         | {10,11}   | radio          |
+----+----------+------------------+-----------+-----------+----------------+

我正在尝试制定一个查询，该查询将提供给定数据的预期分层顺序，如下所示：group_id

预期成果：

+-------+---------+-----------+------------------+--------------------+
| mp.id | mp.name | parent_id |    mpg.title     | mpg.expansion_type |
+-------+---------+-----------+------------------+--------------------+
|     0 | null    | null      | Group 1          | checkbox           |
|     2 | m2      | 0         | null             | null               |
|     3 | m3      | 0         | null             | null               |
|     4 | m4      | 0         | Subgroup 1.1     | radio              |
|     5 | m5      | 4         | Subgroup 1.1.1   | radio              |
|     6 | m6      | 4         | null             | null               |
|     8 | m8      | 5         | Subgroup 1.1.1.1 | radio              |
|     9 | m9      | 5         | null             | null               |
|    10 | m10     | 8         | null             | null               |
|    11 | m11     | 8         | null             | null               |
+-------+---------+-----------+------------------+--------------------+

您能协助我为此编写适当的查询吗？先谢谢你。