3

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)

table_image

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 :)

Improve this question
5
  • Does anything prevent you from casting your values to text, do a replace and then cast back to ltree? Like SELECT replace('a.b.c'::ltree::text, 'b', 'd')::ltree; Commented Feb 2, 2015 at 13:25
  • no, I'm free to use text too! Commented Feb 2, 2015 at 13:26
  • But, there's a catch. Say, some row has this value: Test_Case_Create.Case. When you replace Case with something else, the row will have the value: Test_X_Create.X which is not what I want. I want it to become Test_Case_Create.X Commented Feb 2, 2015 at 13:28
  • In an x.y.x.z ltree it does not make much sense to replace both x. Typically you'd want to replace a subpath anchored at left by another subpath, for example x.y.x.* by x.y.t.* (as opposed to x by t) Commented Feb 2, 2015 at 18:13
  • Well it's not a x.y.x.z tree. Its just that, one label i.e x maybe inside of the word (or more specifically a label) y Commented Feb 2, 2015 at 18:31

2 Answers 2

Reset to default
2

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}';
Improve this answer
1
  • Yes, this is a nice solution with ltree-only functionality. Mine is a bit less advanced but more readable :) Commented Feb 3, 2015 at 9:20
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.

Improve this answer
2
  • Hey thanks for such detailed answer. I'll let you know if I was successful and have your answer as accepted. BTW, can you tell me what lquery is? And what does ~ operator exactly do? Thanks again :) Commented Feb 2, 2015 at 16:04
  • 2
    I cannot, but the docs can ;) postgresql.org/docs/9.4/static/ltree.html Commented Feb 2, 2015 at 16:07

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.