How to update the parent/child of all rows in the tree (ltree)?

Question

Here are my configurations:
DB type: postgresql (9.3 if that's necessary)
Table name: product_sections
Column names: section_id (integer) & section_path (ltree)

Detail:
I have one reference value: section_id: 15 & section_path: Automation_Solutionz. So, when I want to rename Automation_Solutionz to, say Chrome, I want all references of Automation_Solutionz to be updated.

FROM:

Automation_Solutionz
Automation_Solutionz.Test_Case_Creation
Automation_Solutionz.Test_Case_Creation.Automation
Automation_Solutionz.Test_Case_Creation.Manual

TO:

Chrome
Chrome.Test_Case_Creation
Chrome.Test_Case_Creation.Automation
Chrome.Test_Case_Creation.Manual

---

Similarly, when I update a value which is in the middle, it should rename those too. In the case above, Test_Case_Creation should be renamed to something like TestCase or anything else. The same goes for any item which is either in the middle, end or at the start.

Hope, I explained things correctly. Thanks in advance :)

Answer 1

Another approach to achieve this ....

If the replaced text is only one level tree

update product_sections 
set section_path = 'Chrome' 
where section_path = 'Automation_Solutionz'

If the replaced text is at the beginning

update product_sections 
set section_path = 'Chrome' || subpath(section_path,1) 
where section_path ~ 'Automation_Solutionz.*'

If the replaced text is in the middle

update product_sections 
set section_path = subpath(section_path, 0, index(section_path, 'Test_Case_Creation')) || 
                   'Testcase' || 
                   subpath(section_path, index(section_path, 'Test_Case_Creation') + 1) 
where section_path ~ '*.Test_Case_Creation.*{1,}';

If the replaced text is at the end

update product_sections 
set section_path = subpath(section_path, 0, index(section_path, 'Test_Case_Creation')) || 
                   'Testcase' 
where section_path ~ '*.Test_Case_Creation.*{0}';

Answer 2

One can view the ltree data type as a simple text with some constraints. The most important of these constraints is that a label is bordered by dots if part of a label path. Obviously, if it stands at either end, one dot will be missing; when alone, the dots are completely missing.

Fortunately, casting between the two types is easy. This way we can set up a small set of rules for handling replacement, using lquery to find the cases:

-- 1. replace when the label is the whole labelpath
replace(section_path::text, 'to_be_replaced', 'replacement') 
    WHERE section_path ~ 'to_be_replaced'::lquery

-- 2. replace when the label is at the end
replace(section_path::text, '.to_be_replaced', '.replacement')
    WHERE section_path ~ '*.to_be_replaced'::lquery

-- 3. replace when the label is at the beginning
replace(section_path::text, 'to_be_replaced.', '.replacement')
    WHERE section_path ~ 'to_be_replaced.*'::lquery

-- 4. replace when the label is in the middle
replace(section_path::text, '.to_be_replaced.', '.replacement.')
    WHERE section_path ~ '*.to_be_replaced.*'::lquery

In cases 2-4, mind the dots in the search and replacement values of replace(). This way you can be sure you always replace the whole label, not just parts of it.