Home > mailing lists

Re: Effective query for listing flags in use by messages in a folder - Mailing list pgsql-sql

From	Hector Vass
Subject	Re: Effective query for listing flags in use by messages in a folder
Date	March 18, 2015 12:55:15
Msg-id	1426683287617.19714@metametrics.co.uk Whole thread Raw
In response to	Re: Effective query for listing flags in use by messages in a folder (Andreas Joseph Krogh <andreas@visena.com>)
Responses	Re: Effective query for listing flags in use by messages in a folder
List	pgsql-sql

Tree view

OK I get it ..

drop type if exists myflags cascade;

create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');

drop table if exists message;

create table message(

folder_id integer not NULL,

msg varchar(200),

is_flag myflags

);

insert into message values

(1,'msg b','is_seen'),

(1,'msg c','is_seen'),

(1,'msg d','is_seen'),

(1,'msg d','is_replied'),

(1,'msg e','is_seen'),

(1,'msg e','is_replied'),

(1,'msg h','is_deleted')

;

select is_flag from message where folder_id=1 group by 1;

work=# select is_flag from message where folder_id=1 group by 1;

is_flag

------------

is_seen

is_replied

is_deleted

(3 rows)

Hector Vass

+44(0)7773 352 559

* Metametrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ

8 www.metametrics.co.uk

From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Joseph Krogh <andreas@visena.com>
Sent: 18 March 2015 12:20
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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.vass@metametrics.co.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_replied boolean 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 index message_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_idx ON message(folder_id) where is_flagged = TRUE;
create index message_folder_id_draft_idx ON message(folder_id) where is_draft = TRUE;

select 'is_deleted' as falgs from (select * from message where folder_id = 1 AND is_deleted limit 1) as q
UNION
select 'is_forwarded' from (select * from message where folder_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 AND is_seen limit 1) as q
UNION
select 'is_flagged' from (select * from message where folder_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
;