User Tools

Site Tools


support_functions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
support_functions [2012/02/22 16:30]
daniel created
support_functions [2018/04/20 13:17] (current)
daniel Add child_tags
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 19: Line 20:
   END IF;   END IF;
 END; END;
-$$ LANGUAGE plpgsql;+$$ 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
 +     ​RETURN 1;
 +  ELSE
 +     ​RETURN 1+tag_depth(id);​
 +  END IF;
 +END;
 +$$ LANGUAGE plpgsql STABLE STRICT;
 +</​file>​
 +
 +====== 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:​=null;​
 + ​component text;
 +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;
 +</​file>​
 +
 +====== 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.
 +
 +<file sql get_header_line.sql>​
 +CREATE FUNCTION get_header_line(int,​ text) RETURNS SETOF text
 +AS $$
 + ​SELECT (regexp_matches(lines,​ '​^'​||$2||':​ (.*?​)$',​ '​gni'​))[1]
 +   FROM header WHERE mail_id=$1;
 +$$ LANGUAGE sql;
 +
 +</​file>​
support_functions.1329924615.txt.gz · Last modified: 2012/02/22 16:30 by daniel