support_functions
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
support_functions [2012/06/17 22:37] – daniel | support_functions [2012/08/25 07:54] – [tag_path(int): extract the tag name with its hierarchy] 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. | ||
Line 26: | Line 27: | ||
< | < | ||
will retrieve all tags sorted and formatted as in the " | will retrieve all tags sorted and formatted as in the " | ||
- | + | ====== tag_depth(int): returns the depth of a tag inside its hierarchy ====== | |
- | ====== tag_depth(): | + | |
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 45: | Line 45: | ||
| | ||
END IF; | END IF; | ||
+ | END; | ||
+ | $$ LANGUAGE plpgsql STABLE STRICT; | ||
+ | </ | ||
+ | |||
+ | ====== tag_id(text): | ||
+ | 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; | END; | ||
$$ LANGUAGE plpgsql STABLE STRICT; | $$ LANGUAGE plpgsql STABLE STRICT; |
support_functions.txt · Last modified: 2018/04/20 11:17 by daniel