This shows you the differences between two versions of the page.
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] (current) 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> | ||