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/06/18 00:37]
daniel
support_functions [2018/04/20 13:17]
daniel Add child_tags
Line 1: Line 1:
-====== tag_path(): 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 27: Line 28:
 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(): 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>​
  
-====== ​get_header_line(): extracts entries ​from a mail header ​======+====== ​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). 
 +If the name is not found, it returns null. 
 +<file sql tag_id.sql>​ 
 +CREATE OR REPLACE FUNCTION tag_id(in_tag_path text) RETURNS integer AS  
 +$$ 
 +DECLARE 
 + id INTEGER:=null; 
 + ​component text; 
 +BEGIN 
 +  FOR component IN SELECT regexp_split_to_table(in_tag_path,​ '​->'​) 
 +  LOOP 
 +    SELECT tag_id FROM tags WHERE upper(NAME)=upper(component) AND parent_id is not distinct ​from id 
 +      INTO id;     
 +  END LOOP; 
 +  RETURN id; 
 +END; 
 +$$ LANGUAGE plpgsql STABLE STRICT; 
 +</​file>​ 
 + 
 +====== 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 55: 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