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/02/27 21:52]
daniel
support_functions [2018/04/20 13:17] (current)
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.
  
-<code sql>+<file sql tag_path.sql>
 CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS  CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS 
 $$ $$
Line 21: Line 22:
 $$ LANGUAGE plpgsql STABLE; $$ LANGUAGE plpgsql STABLE;
  
-</code>+</file>
  
 Example: Example:
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.
  
-<code sql>+<file sql tag_depth.sql>
 CREATE OR REPLACE FUNCTION tag_depth(in_tag_id INTEGER) RETURNS INT AS  CREATE OR REPLACE FUNCTION tag_depth(in_tag_id INTEGER) RETURNS INT AS 
 $$ $$
Line 47: Line 50:
 END; END;
 $$ LANGUAGE plpgsql STABLE STRICT; $$ LANGUAGE plpgsql STABLE STRICT;
-</code>+</file> 
 + 
 +====== 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 a 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. 
 + 
 +<file sql get_header_line.sql>​ 
 +CREATE FUNCTION get_header_line(int,​ text) RETURNS SETOF text 
 +AS $$ 
 + ​SELECT (regexp_matches(lines,​ '​^'​||$2||':​ (.*?​)$',​ '​gni'​))[1] 
 +   FROM header WHERE mail_id=$1;​ 
 +$$ LANGUAGE sql; 
 + 
 +</file>
support_functions.1330375971.txt.gz · Last modified: 2012/02/27 21:52 by daniel