Differences

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

--- support_functions [2012/02/22 15:33] – 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.
-<code sql>
+<file sql tag_path.sql>
 CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS
 $$
@@ Line 21: / Line 22: @@
 $$ LANGUAGE plpgsql STABLE;
-</code>
+</file>
 Example:
@@ Line 28: / Line 29: @@
+====== 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.
+<file sql tag_depth.sql>
+CREATE OR REPLACE FUNCTION tag_depth(in_tag_id INTEGER) RETURNS INT AS
+$$
+DECLARE
+ id INTEGER;
+BEGIN
+  IF in_tag_id IS NULL THEN
+    RETURN NULL;
+  END IF;
+  SELECT parent_id INTO id FROM tags WHERE tag_id=in_tag_id;
+  IF (id IS NULL) THEN
+     RETURN 1;
+  ELSE
+     RETURN 1+tag_depth(id);
+  END IF;
+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>
+====== 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>