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 revisionPrevious revision
Next revision
Previous revision
sql_analysis [2016/10/04 16:17] – [Duplicate messages] danielsql_analysis [2019/01/24 17:55] (current) – [Presence and count of specific mail headers] daniel
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 17:55 by daniel