Differences

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

--- support_functions [2012/05/29 00:11] – daniel
+++ support_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.
@@ 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 47: / Line 50: @@
 END;
 $$ LANGUAGE plpgsql STABLE STRICT;
+</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>