User Tools

Site Tools


support_functions

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
support_functions [2012/08/25 09:54]
daniel [tag_path(int): extract the tag name with its hierarchy]
support_functions [2018/04/20 13:17]
daniel Add child_tags
Line 27: 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 52:
 </​file>​ </​file>​
  
-====== tag_id(text): returns ​the ID of a tag from its full hierarchical name (case insensitive) ​======+====== 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 69: Line 73:
 </​file>​ </​file>​
  
-====== ​get_header_line(): extracts entries from mail header ​======+====== ​child_tags(int====== 
 +**Return all childs of tag.** \\ 
 +Takes the ID of a tag or null to designate the root of all tags. Returns 
 +the set of child tags. 
 + 
 +<file sql child_tags.sql>​ 
 +create function child_tags(top_id integer) returns setof integer 
 +as $$ 
 +WITH RECURSIVE tagr(_tag_id) as ( 
 + ​select tag_id 
 + from tags where parent_id is not distinct from top_id 
 +UNION ALL 
 + ​select 
 +   ​tag_id 
 + FROM tags JOIN tagr ON tagr._tag_id=tags.parent_id 
 +
 +select _tag_id FROM tagr; 
 +$$ language sql stable 
 +</​file>​ 
 + 
 +====== get_header_line() ====== 
 +**Extract entries from a mail header.** \\
 This function takes a mail_id and the name of a header field and returns all corresponding header entries for this message. This function takes a mail_id and the name of a header field and returns all corresponding header entries for this message.
  
Line 75: Line 100:
 CREATE FUNCTION get_header_line(int,​ text) RETURNS SETOF text CREATE FUNCTION get_header_line(int,​ text) RETURNS SETOF text
 AS $$ AS $$
- ​SELECT (regexp_matches(lines, ​E'​^'​||$2||':​ (.*?​)$',​ '​gni'​))[1]+ ​SELECT (regexp_matches(lines,​ '​^'​||$2||':​ (.*?​)$',​ '​gni'​))[1]
    FROM header WHERE mail_id=$1;    FROM header WHERE mail_id=$1;
 $$ LANGUAGE sql; $$ LANGUAGE sql;
  
 </​file>​ </​file>​
support_functions.txt · Last modified: 2018/04/20 13:17 by daniel