Postgresql recursive function

As with many recursive functions, we can break this down conceptually into several steps.

First, we have the base case - when given null as input, the query selects all locations with no parent:

SELECT
    ...
FROM "Locations" t
WHERE location_id IS null AND t."ParentLocationId" IS null

Next, we have the repeated case - when given an ID as input, the query selects all locations which have that ID as their parent:

SELECT
    ...
FROM "Locations" t
WHERE t."ParentLocationId" = location_id

Then we add the recursion - when returning either of the above lists, also call the function again, with each ID found:

SELECT
    ...
FROM "Locations" t
LEFT JOIN build_hierarchey(t."Id") AS f(x) ON true

The ON true clause is there because there has to be some condition, but the actual rows to join are determined by the t."Id" passed into the function. It needs to be a LEFT JOIN, because the function will sometimes generate no rows; e.g. with your sample data build_hierarchey(3) will return no rows, but we don't want that to exclude location 3 from the results of build_hierarchey(1).

Now we need to combine the results of the current query (t) with the results of the recursive function call (f):

SELECT
    CASE WHEN COUNT(x) > 0 
        THEN ((to_jsonb(t) || jsonb_build_object('Children', jsonb_agg(f.x)))
        ELSE to_jsonb(t)
    END
FROM ... as t
LEFT JOIN ... as f(x)
GROUP BY t."Id", t."Name";

Again, this has to handle the case where the function returned no rows. The rest is just formatting: flattening each set of results into a JSON object and combining them in the desired structure.

Now, to your questions:

the sequence of execution (it goes deep hierarchy by each root node first or list all root node first then go to next level.)

In SQL, it's rarely useful to talk about order of execution, because a DBMS doesn't generate one row at a time and spit out the result, it tries to generate the requested data as efficiently as possible. As far as I know, the DBMS is free to choose whether all the rows at one level are loaded into memory, and the function run on each; or whether the function is run depth-first as each row is encountered.

Generally speaking, the only thing that is guaranteed is that the results will come out in the order requested by the query. In this case, there is no ORDER BY clause, so the actual order of results at each level is also completely undefined - the DBMS will pick whichever order is most convenient when running the query.

how it breaks from this function.

This function is not actually guaranteed to exit; its exit condition relies on the assumption that your data contains no cycles - that is, that it will eventually reach a node with no "children" (i.e., an ID that never appears in the ParentLocationId column).

To demonstrate a cycle, consider this data:

ID  Name      ParentLocationId
1   "A"       3
2   "B"       1
3   "C"       2

A call to build_hierarchey(1) will proceed as follows:

• Fetch nodes with parent 1: found ID=2
• Fetch nodes with parent 2: found ID=3
• Fetch nodes with parent 3: found ID=1
• Fetch nodes with parent 1: infinite loop

This is probably prevented elsewhere in the application, but we also have one extra protection: by calling build_hierarchey(null) we can never encounter such a cycle. We can prove this as follows:

• Every node has either one parent or none
• A cycle consists of following parents and reaching the same node
• For a node to form part of a cycle, it must have a parent
• Therefore the nodes with null as their parent are not part of a cycle
• If a node is in a cycle, its parent must be in that same cycle (cycle A-B-C-A can be extended to A-B-C-A-B, and so on)
• Therefore, a node in a cycle cannot have a parent whose parent is null, because that parent could not be part of the cycle
• Therefore, starting with a node whose parent is null (which is what build_hierarchey(null) does) will never reach a cycle, even if one exists in the table