sql_analysis
Differences
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> | ||
sql_analysis.1475597874.txt.gz · Last modified: 2016/10/04 18:17 by daniel
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
