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.
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.
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.1330375971.txt.gz · Last modified: 2012/02/27 20:52 by daniel