Extract the tag name with its hierarchy.
This recursive function takes the ID of a tag and returns its full name, including its hierarchy.

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;

Example:

 SELECT tag_id,tag_path(tag_id) FROM tags ORDER BY 2

will retrieve all tags sorted and formatted as in the “Current messsages” tree in the Quick selection panel of the user interface.

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.

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;

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.

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;

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.

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

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.

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;