User Tools

Site Tools


support_functions

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
support_functions [2012/02/22 15:33] danielsupport_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
 +     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.txt · Last modified: 2018/04/20 11:17 by daniel