User Tools

Site Tools


sql_analysis

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
sql_analysis [2016/10/04 18:17]
daniel [Duplicate messages]
sql_analysis [2019/01/24 18:55]
daniel [Presence and count of specific mail headers]
Line 52: Line 52:
 <code sql> <code sql>
 select FIELD||':'​||arr[1],​ count(*) 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, ​        from header, ​
          ​(VALUES ('​X-Priority'​),​ ('​Importance'​),​ ('​Precedence'​),​ ('​Priority'​),​          ​(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 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 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> <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, 
 +  1 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>​ </​code>​
  
  
sql_analysis.txt · Last modified: 2019/01/24 18:55 by daniel