0

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
  • 1
    Hi and welcome to dba.se what rdbms are you using? Could you add it to the tags? Adding some sample data & table definition could help too – Randi Vertongen Oct 10 at 16:43
  • 1
    Show source table's DDL and sample datasets which gives shown results. And use A-chars in the data only. – Akina Oct 10 at 18:36
  • Do those two "Fenggang" entries come from the same row in the table? – CL. Oct 11 at 7:09
  • Hi, thanks for the welcome. Have added DDL and data sources. Not sure what A-chars are but I suspect you meant the Chinese chars. I removed them. and Fenggang entries don't come from the same row, there are different places with the same name And sorry for using UUID, it's really not helping readability and not even proper to use here – coolnodje Oct 11 at 9:12
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.