Differences

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

--- support_functions [2012/06/17 22:37] – daniel
+++ support_functions [2017/05/26 11:46] – [get_header_line()] 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.
@@ 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.
-====== 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.
@@ Line 49: / Line 52: @@
 </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>
+====== 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.
@@ Line 55: / Line 80: @@
 CREATE FUNCTION get_header_line(int, text) RETURNS SETOF text
 AS $$
- SELECT (regexp_matches(lines, E'^'||$2||': (.*?)$', 'gni'))[1]
+ SELECT (regexp_matches(lines, '^'||$2||': (.*?)$', 'gni'))[1]
    FROM header WHERE mail_id=$1;
 $$ LANGUAGE sql;
 </file>