This is an old revision of the document!
Table of Contents
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.
- 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.
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.
- 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;
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.
- 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;
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.
- 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;