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

do you want to post some insert statements to populate the table message with some realistic example data..

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(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org> on behalf of Andreas Joseph Krogh <andreas(at)visena(dot)com>
Sent: 18 March 2015 11:59
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass <hector(dot)vass(at)metametrics(dot)co(dot)uk<mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>:

Andreas ... your code and one of my examples ... I have modified my option 2 to give an example with data that gives you I believe exactly the same output (one row for each flag set for folder_id=3 with the text representation of the flag) ... when you satisfy yourself this produces the same results you might then want to go back and re-read my original post which rather than feeding you verbatim how to produce exactly the same results gave the the pro's and con's of 3x different approaches... I chose to illustrate my option 2 because it is easy to understand and is a reasonable production solution, option 1 was really just to get you thinking differently about how to do this and option 3 I concede was more advanced and probably but requires skills other than plain SQL to implement.

It's not that I didn't read you post, I just don't see how it solves the problem of listing a distinct set of flags being set on messages in a folder. AFAICS your examples list messages with any or a specific set of flags set, which is not what I'm after.

I see now that I didn't specify the "msg"-column so maybe it wasn't clear that the there's only one tuple in "message" for each message and a message may have several flags set.

This is a more realistic table, with "msg" as varchar holding the actual text of the 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
);

--
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>
[cid:part_F764646534471GSEI0S]<https://www.visena.com>