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.

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;

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 NAME=component AND parent_id IS NOT DISTINCT FROM id
      INTO id;    
  END LOOP;
  RETURN id;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

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, E'^'||$2||': (.*?)$', 'gni'))[1]
   FROM header WHERE mail_id=$1;
$$ LANGUAGE SQL;