add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test (
lo_id text,
parent_id text,
display_sequence int
);

INSERT INTO test VALUES
('57b2e67b-5862-499a-a471-0f2f6b23440e', NULL, 0),
('5c51558b-1180-495f-88c3-f7af49bafcf3', '57b2e67b-5862-499a-a471-0f2f6b23440e', 1),
('3962f997-9e14-4cac-a95f-dc20c077a531', '57b2e67b-5862-499a-a471-0f2f6b23440e', 2),
('78b6cac3-307f-449a-bb3c-fe4442f4d1e8', '5c51558b-1180-495f-88c3-f7af49bafcf3', 3),
('7ecb01da-078a-49b9-b488-86dc6bb24ab4', '5c51558b-1180-495f-88c3-f7af49bafcf3', 4),
('62e889fe-b0e6-4fd4-a89f-512a1f31e210', '5c51558b-1180-495f-88c3-f7af49bafcf3', 5),
('6346657d-a41e-45ab-87bc-abf32a8c616c', '3962f997-9e14-4cac-a95f-dc20c077a531', 6);

SELECT * FROM test;
lo_id parent_id display_sequence
57b2e67b-5862-499a-a471-0f2f6b23440e null 0
5c51558b-1180-495f-88c3-f7af49bafcf3 57b2e67b-5862-499a-a471-0f2f6b23440e 1
3962f997-9e14-4cac-a95f-dc20c077a531 57b2e67b-5862-499a-a471-0f2f6b23440e 2
78b6cac3-307f-449a-bb3c-fe4442f4d1e8 5c51558b-1180-495f-88c3-f7af49bafcf3 3
7ecb01da-078a-49b9-b488-86dc6bb24ab4 5c51558b-1180-495f-88c3-f7af49bafcf3 4
62e889fe-b0e6-4fd4-a89f-512a1f31e210 5c51558b-1180-495f-88c3-f7af49bafcf3 5
6346657d-a41e-45ab-87bc-abf32a8c616c 3962f997-9e14-4cac-a95f-dc20c077a531 6 
SELECT 7
CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
_json_output jsonb;
_temprow record;
BEGIN
SELECT
jsonb_build_object('Name', lo_id, 'Subclasses', array_to_json(ARRAY[]::text[]))
INTO _json_output
FROM test
WHERE parent_id IS NULL;
FOR _temprow IN
WITH RECURSIVE tree(lo_id, ancestor, child, path, json) AS (
SELECT
t1.lo_id,
NULL::text,
t2.lo_id,
'{Subclasses}'::text[] || (row_number() OVER (PARTITION BY t1.lo_id ORDER BY t2.display_sequence) - 1)::text,
jsonb_build_object('Name', t2.lo_id, 'Subclasses', array_to_json(ARRAY[]::text[]))
FROM test t1
LEFT JOIN test t2 ON t1.lo_id = t2.parent_id
WHERE t1.parent_id IS NULL

UNION

SELECT
t1.lo_id,
t1.parent_id,
t2.lo_id,
tree.path || '{Subclasses}' || (row_number() OVER (PARTITION BY t1.lo_id ORDER BY t2.display_sequence) - 1)::text,
jsonb_build_object('Name', t2.lo_id, 'Subclasses', array_to_json(ARRAY[]::text[]))
FROM test t1
LEFT JOIN test t2 ON t1.lo_id = t2.parent_id
INNER JOIN tree ON (t1.lo_id = tree.child)
WHERE t1.parent_id = tree.lo_id
)
CREATE FUNCTION
SELECT jsonb_pretty(json_tree());
jsonb_pretty
{
    "Name": "57b2e67b-5862-499a-a471-0f2f6b23440e",
    "Subclasses": [
        {
            "Name": "5c51558b-1180-495f-88c3-f7af49bafcf3",
            "Subclasses": [
                {
                    "Name": "78b6cac3-307f-449a-bb3c-fe4442f4d1e8",
                    "Subclasses": [
                    ]
                },
                {
                    "Name": "7ecb01da-078a-49b9-b488-86dc6bb24ab4",
                    "Subclasses": [
                    ]
                },
                {
                    "Name": "62e889fe-b0e6-4fd4-a89f-512a1f31e210",
                    "Subclasses": [
                    ]
                }
            ]
        },
        {
            "Name": "3962f997-9e14-4cac-a95f-dc20c077a531",
            "Subclasses": [
                {
                    "Name": "6346657d-a41e-45ab-87bc-abf32a8c616c",
                    "Subclasses": [
                    ]
                }
            ]
        }
    ]
}		 
SELECT 1