Differences

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

--- support_functions [2012/08/25 07:55] – daniel
+++ support_functions [2018/04/20 11:17] (current) – Add child_tags daniel
@@ Line 71: / Line 71: @@
 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>
@@ Line 80: / Line 100: @@
 CREATE FUNCTION get_header_line(int, text) RETURNS SETOF text
 AS $$
- SELECT (regexp_matches(lines, E'^'||$2||': (.*?)$', 'gni'))[1]
+ SELECT (regexp_matches(lines, '^'||$2||': (.*?)$', 'gni'))[1]
    FROM header WHERE mail_id=$1;
 $$ LANGUAGE sql;
 </file>