support_functions
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
support_functions [2012/02/22 15:33] – daniel | support_functions [2018/04/20 11:17] (current) – Add child_tags daniel | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== tag_path(): extract the tag name with its hierarchy | + | ====== 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. | This recursive function takes the ID of a tag and returns its full name, including its hierarchy. | ||
- | <code sql> | + | <file sql tag_path.sql> |
CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS | CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS | ||
$$ | $$ | ||
Line 21: | Line 22: | ||
$$ LANGUAGE plpgsql STABLE; | $$ LANGUAGE plpgsql STABLE; | ||
- | </code> | + | </file> |
Example: | Example: | ||
Line 28: | Line 29: | ||
+ | ====== 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. | ||
+ | |||
+ | <file sql 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 | ||
+ | | ||
+ | ELSE | ||
+ | | ||
+ | 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. | ||
+ | <file sql tag_id.sql> | ||
+ | CREATE OR REPLACE FUNCTION tag_id(in_tag_path text) RETURNS integer AS | ||
+ | $$ | ||
+ | DECLARE | ||
+ | id INTEGER: | ||
+ | | ||
+ | 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; | ||
+ | </ | ||
+ | |||
+ | ====== child_tags(int) ====== | ||
+ | **Return all childs of a tag.** \\ | ||
+ | Takes the ID of a tag or null to designate the root of all tags. Returns | ||
+ | the set of child tags. | ||
+ | |||
+ | <file sql child_tags.sql> | ||
+ | create function child_tags(top_id integer) returns setof integer | ||
+ | as $$ | ||
+ | WITH RECURSIVE tagr(_tag_id) as ( | ||
+ | | ||
+ | from tags where parent_id is not distinct from top_id | ||
+ | UNION ALL | ||
+ | | ||
+ | | ||
+ | FROM tags JOIN tagr ON tagr._tag_id=tags.parent_id | ||
+ | ) | ||
+ | select _tag_id FROM tagr; | ||
+ | $$ language sql stable | ||
+ | </ | ||
+ | |||
+ | ====== 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. | ||
+ | |||
+ | <file sql get_header_line.sql> | ||
+ | CREATE FUNCTION get_header_line(int, | ||
+ | AS $$ | ||
+ | | ||
+ | FROM header WHERE mail_id=$1; | ||
+ | $$ LANGUAGE sql; | ||
+ | |||
+ | </ |
support_functions.1329924791.txt.gz · Last modified: 2012/02/22 15:33 by daniel