Re: Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 13:08:45, skrev Hector Vass <
hector(dot)vass(at)metametrics(dot)co(dot)uk <mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>: do you
want to post some insert statements to populate the table message with
some realistic example data..

  Sure: drop table if EXISTS message; create table message( folder_id integer
not NULL, msg varchar NOT NULL, is_seen boolean NOT NULL default false,
is_repliedboolean not null default false, is_forwarded boolean not null default
false, is_deleted boolean not null default false, is_draft boolean not null
default false, is_flagged boolean not null default false ); INSERT INTO message(
folder_id, msg, is_seen, is_replied, is_forwarded, is_deleted, is_draft,
is_flagged) values(1, 'msg a', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1,
'msg b', TRUE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1, 'msg c', TRUE, FALSE,
FALSE, FALSE, FALSE, FALSE) , (1, 'msg d', TRUE, TRUE, FALSE, FALSE, FALSE,
FALSE) , (1, 'msg e', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE) , (1, 'msg f',
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1, 'msg g', FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE) , (1, 'msg h', TRUE, FALSE, FALSE, TRUE, FALSE, FALSE) ;
create indexmessage_folder_id_deleted_idx ON message(folder_id) where is_deleted
=TRUE; create index message_folder_id_forwarded_idx ON message(folder_id) where
is_forwarded= TRUE; create index message_folder_id_replied_idx ON message(
folder_id) where is_replied = TRUE; create index message_folder_id_seen_idx ON
message(folder_id) where is_seen = TRUE; create index
message_folder_id_flagged_idxON message(folder_id) where is_flagged = TRUE;
create indexmessage_folder_id_draft_idx ON message(folder_id) where is_draft =
TRUE; select 'is_deleted' as falgs from (select * from message where folder_id =
1AND is_deleted limit 1) as q UNION select 'is_forwarded' from (select * from
message wherefolder_id = 1 AND is_forwarded limit 1) as q UNION select
'is_replied'from (select * from message where folder_id = 1 AND is_replied limit
1) as q UNION select 'is_seen' from (select * from message where folder_id = 1
ANDis_seen limit 1) as q UNION select 'is_flagged' from (select * from message
wherefolder_id = 1 AND is_flagged limit 1) as q UNION select 'is_draft' from (
select* from message where folder_id = 1 AND is_draft limit 1) as q ; Yields:  
   falgs
------------
 is_deleted
 is_replied
 is_seen
(3 rows)     -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47
909 56 963 andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com> www.visena.com
<https://www.visena.com> <https://www.visena.com>