support_functions
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
support_functions [2012/08/23 01:56] daniel Add tag_id() |
support_functions [2018/04/20 13:17] daniel Add child_tags |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== tag_path(int): 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. | ||
| Line 27: | Line 28: | ||
| will retrieve all tags sorted and formatted as in the "Current messsages" tree in the Quick selection panel of the user interface. | 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 ====== | + | |
| + | ====== 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. | This recursive function takes the ID of a tag and returns its depth inside its hierarchy, starting by 1 for top-level tags. | ||
| Line 49: | Line 52: | ||
| </file> | </file> | ||
| - | ====== tag_id(text): returns the ID of a tag from its full hierarchical name ====== | + | ====== 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). | This is the inverse function of tag_path(int). | ||
| If the name is not found, it returns null. | If the name is not found, it returns null. | ||
| Line 61: | Line 65: | ||
| FOR component IN SELECT regexp_split_to_table(in_tag_path, '->') | FOR component IN SELECT regexp_split_to_table(in_tag_path, '->') | ||
| LOOP | LOOP | ||
| - | SELECT tag_id FROM tags WHERE NAME=component AND parent_id is not distinct from id | + | SELECT tag_id FROM tags WHERE upper(NAME)=upper(component) AND parent_id is not distinct from id |
| INTO id; | INTO id; | ||
| END LOOP; | END LOOP; | ||
| Line 69: | Line 73: | ||
| </file> | </file> | ||
| - | ====== get_header_line(): extracts entries from a mail header ====== | + | ====== 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 ( | ||
| + | select tag_id | ||
| + | from tags where parent_id is not distinct from top_id | ||
| + | UNION ALL | ||
| + | select | ||
| + | tag_id | ||
| + | FROM tags JOIN tagr ON tagr._tag_id=tags.parent_id | ||
| + | ) | ||
| + | select _tag_id FROM tagr; | ||
| + | $$ language sql stable | ||
| + | </file> | ||
| + | |||
| + | ====== 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. | This function takes a mail_id and the name of a header field and returns all corresponding header entries for this message. | ||
| Line 75: | Line 100: | ||
| CREATE FUNCTION get_header_line(int, text) RETURNS SETOF text | CREATE FUNCTION get_header_line(int, text) RETURNS SETOF text | ||
| AS $$ | AS $$ | ||
| - | SELECT (regexp_matches(lines, E'^'||$2||': (.*?)$', 'gni'))[1] | + | SELECT (regexp_matches(lines, '^'||$2||': (.*?)$', 'gni'))[1] |
| FROM header WHERE mail_id=$1; | FROM header WHERE mail_id=$1; | ||
| $$ LANGUAGE sql; | $$ LANGUAGE sql; | ||
| </file> | </file> | ||
support_functions.txt · Last modified: 2018/04/20 13:17 by daniel
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
