How to have CTE query recurse parent query result one by one?

Question

I have this query used on Postgres to return place administrative levels.

WITH RECURSIVE hierarchy(name, pinyin, level) AS (
    SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk 
    from place p 
    where p.pinyin = 'Jiulong'
    UNION ALL
    SELECT up.name, up.pinyin, up.level, up.upper_place_object_id_fk 
    from hierarchy h
    INNER JOIN place up ON h.upper_place_object_id_fk = up.object_id
    WHERE h.upper_place_object_id_fk IS NOT NULL
)
SELECT h.name, h.pinyin, h.level from hierarchy h;

It works just fine for a single match, e.g.

"Jiulong"   4
"Yingde"    3
"Qingyuan"  2
"Guangdong" 1

But of course, there can be several places with the same name (and a different position in the admin hierarchy)

"Fenggang"  4
"Fenggang"  4
"Huaiji"    3
"Dongguan"  2
"Zhaoqing"  2
"Guangdong" 1
"Guangdong" 1

In this case the result doesn't make sense as I can't reconstruct the proper hierarchy. And that's because the recursion occurs for every result of the parent query at the same time.

It'd work if the recursion could be done until the exit condition for each result of the parent.

It is possible to control how the recursion is done?

Place Table script

CREATE TABLE public.place
(
    object_id uuid NOT NULL DEFAULT uuid_generate_v4(),
    upper_place_object_id_fk uuid,
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    level smallint NOT NULL,
    CONSTRAINT pk_place PRIMARY KEY (object_id),
    CONSTRAINT unique_place UNIQUE (name, upper_place_object_id_fk, level)
,
    CONSTRAINT fk_place_upper_pla_place FOREIGN KEY (upper_place_object_id_fk)
        REFERENCES public.place (object_id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
        NOT VALID
)

and here are the relevant data from the table

object_id                               upper_place_object_id_fk                 name      level 
"540ed8e9-c456-4a46-8049-1332f4ab52c1"  NULL                                    "Guangdong" 1   false
"4f7c7071-f917-4ab8-82c9-75aff0ba44d0"  "540ed8e9-c456-4a46-8049-1332f4ab52c1"  "Dongguan"  2   false
"afce8ec3-599f-4cea-9227-29bb750d4b01"  "540ed8e9-c456-4a46-8049-1332f4ab52c1"  "Zhaoqing"  2   false
"714b8e23-c346-4370-8697-25bb09497342"  "afce8ec3-599f-4cea-9227-29bb750d4b01"  "Huaiji "   3   false
"0461963d-e886-4149-bded-ee08216dd374"  "714b8e23-c346-4370-8697-25bb09497342"  "Fenggang"  4   false
"6955f89b-b7d3-4b1e-b21e-fb584d7f165b"  "712c1f45-24ac-4711-a47f-22513afc6fdd"  "Dongguan"  4   true
"d931f691-0ec7-4fc7-9197-b15461e984e0"  "4f7c7071-f917-4ab8-82c9-75aff0ba44d0"  "Fenggang"  4   true

score 0 · Answer 1 · answered Oct 15 '19 at 14:53

It does not seem possible to get control on how the recursion happens in CTE.

One solution to disentangle results is to add info on the parent query and build a 'path' for each result.

As shown in this post: How do I sort the results of a recursive query in an expanded tree-like fashion?

This seems to me the most acceptable way at the moment, but I'm still looking for a way to be able to directly get more concise results.

How to have CTE query recurse parent query result one by one?

1 Answers1