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 revisionPrevious revision
Next revision
Previous revision
support_functions [2012/02/27 20:52] danielsupport_functions [2018/04/20 11:17] (current) – Add child_tags daniel
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 20:52 by daniel