agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: 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:08:45 +0000
Message-ID: <1426680525528.15871@metametrics.co.uk> (raw)
In-Reply-To: <VisenaEmail.2e.91ce785d8680758c.14c2cb41a80@tc7-visena>
References: <1426676819968.86115@metametrics.co.uk>
<VisenaEmail.2e.91ce785d8680758c.14c2cb41a80@tc7-visena>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Joseph Krogh <andreas@visena.com>
Sent: 18 March 2015 11:59
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. 12:07:00, skrev Hector Vass <hector.vass@metametrics.co.uk<mailto:hector.vass@metametrics.co.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@visena.com<mailto:andreas@visena.com>
www.visena.com<https://www.visena.com;
[cid:part_F764646534471GSEI0S]<https://www.visena.com;
Attachments:
[image/png] ATT00001.png (1.9K, ../1426680525528.15871@metametrics.co.uk/3-ATT00001.png)
download | view image
view thread (12+ messages) latest in thread
Message-ID: <1426680525528.15871@metametrics.co.uk>
Permalink: ../1426680525528.15871@metametrics.co.uk/
Also on: postgresql.org/message-id/1426680525528.15871@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: <1426680525528.15871@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