Manitou-Mail Wiki

User Tools

Site Tools

Trace:
support_functions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Next revisionBoth sides next revision
support_functions [2012/08/22 23:56] – Add tag_id() danielsupport_functions [2012/08/25 07:54] – [tag_path(int): extract the tag name with its hierarchy] daniel
Line 1: Line 1:
-====== tag_path(int): extract the tag name with its hierarchy ======+====== tag_path(int) ====== 
 +**Extract the tag name with its hierarchy.** \\
 This recursive function takes the ID of a tag and returns its full name, including its hierarchy. This recursive function takes the ID of a tag and returns its full name, including its hierarchy.
  
Line 26: Line 27:
 <code> SELECT tag_id,tag_path(tag_id) FROM tags ORDER BY 2</code> <code> SELECT tag_id,tag_path(tag_id) FROM tags ORDER BY 2</code>
 will retrieve all tags sorted and formatted as in the "Current messsages" tree in the Quick selection panel of the user interface. will retrieve all tags sorted and formatted as in the "Current messsages" tree in the Quick selection panel of the user interface.
- 
 ====== tag_depth(int): returns the depth of a tag inside its hierarchy ====== ====== tag_depth(int): returns the depth of a tag inside its hierarchy ======
 This recursive function takes the ID of a tag and returns its depth inside its hierarchy, starting by 1 for top-level tags. This recursive function takes the ID of a tag and returns its depth inside its hierarchy, starting by 1 for top-level tags.
Line 49: Line 49:
 </file> </file>
  
-====== tag_id(text): returns the ID of a tag from its full hierarchical name  ======+====== tag_id(text): returns the ID of a tag from its full hierarchical name (case insensitive) ======
 This is the inverse function of tag_path(int). This is the inverse function of tag_path(int).
 If the name is not found, it returns null. If the name is not found, it returns null.
Line 61: Line 61:
   FOR component IN SELECT regexp_split_to_table(in_tag_path, '->')   FOR component IN SELECT regexp_split_to_table(in_tag_path, '->')
   LOOP   LOOP
-    SELECT tag_id FROM tags WHERE NAME=component AND parent_id is not distinct from id+    SELECT tag_id FROM tags WHERE upper(NAME)=upper(component) AND parent_id is not distinct from id
       INTO id;           INTO id;    
   END LOOP;   END LOOP;
support_functions.txt · Last modified: 2018/04/20 11:17 by daniel