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

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.