User Tools

Site Tools


support_functions

This is an old revision of the document!


tag_path(): 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(): 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;
support_functions.1338250277.txt.gz · Last modified: 2012/05/29 02:11 by daniel