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

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Hector Vass <hector.vass@metametrics.co.uk>
To: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Effective query for listing flags in use by messages in a folder
Date: Wed, 18 Mar 2015 12:54:50 +0000
Message-ID: <1426683287617.19714@metametrics.co.uk> (raw)
In-Reply-To: <VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena>
References: <1426680525528.15871@metametrics.co.uk>
	<VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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
*   www.metametrics.co.uk<http://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<mailto: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
;



Yields:

   falgs
------------
 is_deleted
 is_replied
 is_seen
(3 rows)


--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com<mailto:andreas@visena.com>
www.visena.com<https://www.visena.com;
[cid:part_F764646561758A3NHED]<https://www.visena.com;

Attachments:

  [image/png] ATT00001.png (1.9K, ../1426683287617.19714@metametrics.co.uk/3-ATT00001.png)
  download | view image

view thread (12+ messages)  latest in thread

Message-ID: <1426683287617.19714@metametrics.co.uk>
Permalink:  ../1426683287617.19714@metametrics.co.uk/
Also on:    postgresql.org/message-id/1426683287617.19714@metametrics.co.uk

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: hector.vass@metametrics.co.uk, andreas@visena.com
  Subject: Re: Effective query for listing flags in use by messages in a folder
  In-Reply-To: <1426683287617.19714@metametrics.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox