====== 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.

<file sql tag_path.sql>
CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS 
$$
DECLARE
 r text;
 id INTEGER;
BEGIN
  IF in_tag_id IS NULL THEN
    RETURN null;
  END IF;
  SELECT name, parent_id INTO r,id FROM tags WHERE tag_id=in_tag_id;
  IF (id IS NULL) THEN
     RETURN r;
  ELSE
     RETURN tag_path(id)||'->'||coalesce(r,'');
  END IF;
END;
$$ LANGUAGE plpgsql STABLE;

</file>

Example:
<code> SELECT tag_id,tag_path(tag_id) FROM tags ORDER BY 2</code>
will retrieve all tags sorted and formatted as in the "Current messsages" tree in the Quick selection panel of the user interface.


====== 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>

====== 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>