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 revision Previous revision
Next revision
Previous revision
support_functions [2012/08/25 09:55]
daniel
support_functions [2018/04/20 13:17]
daniel Add child_tags
Line 71: Line 71:
 END; END;
 $$ LANGUAGE plpgsql STABLE STRICT; $$ 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>​ </​file>​
  
Line 80: 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