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
Last revision Both sides next revision
sql_analysis [2016/10/04 18:17]
daniel [Duplicate messages]
sql_analysis [2018/04/20 11:47]
daniel
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