Differences

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

--- sql_analysis [2016/10/04 16:17] – [Duplicate messages] daniel
+++ sql_analysis [2019/01/24 17:55] (current) – [Presence and count of specific mail headers] daniel
@@ Line 52: / Line 52: @@
 <code sql>
 select FIELD||':'||arr[1], count(*)
- from (select FIELD, regexp_matches(lines, '(?:^|\n)' || FIELD || ':\s*([^\n]*)\n', 'g') as arr
+ from (select FIELD, regexp_matches(lines, '(?:^|\n)' || FIELD || ':\s*([^\n]*)', 'gi') as arr
        from header,
          (VALUES ('X-Priority'), ('Importance'), ('Precedence'), ('Priority'),
@@ Line 80: / Line 80: @@
 The ''IS NOT DISTINCT'' comparator behaves as expected when ''bodytext'' or ''bodyhtml'' is NULL, as opposed
 to the simple equality operator, for which ''NULL=NULL'' is false
-====== Messages with specific attachment types ======
-To retrieve all messages containing pdf files or any image file:
+====== Hierarchical view of tags  ======
+Output a list of tags sorted by hierarchy level and names,
+with an indentation to represent the hierarchy.
 <code sql>
-select distinct mail_id FROM attachments WHERE content_type='application/pdf' OR content_type like 'image/%';
+WITH RECURSIVE tagr(a,_tag_id,name,level) as (
-</code>
+ select
+  array[row_number() over (order by name)] as a,
+  tag_id,
+  name,
+as level
+ from tags where parent_id is null
+UNION ALL
+ select
+   tagr.a || row_number() over (order by tags.name),
+   tag_id,
+   tags.name,
+   tagr.level+1
+ FROM tags JOIN tagr ON tagr._tag_id=tags.parent_id
+)
+select repeat('   ', level-1) || name from tagr order by a;
-====== Messages sent or received today  ======
-<code>
-select mail_id from mail where msg_date>=date_trunc('day',now());
 </code>