Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYCsb-0002oG-OG for pgsql-sql@arkaria.postgresql.org; Wed, 18 Mar 2015 12:15:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYCsa-0006UX-VV for pgsql-sql@arkaria.postgresql.org; Wed, 18 Mar 2015 12:15:21 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYCsY-0006Qz-UZ for pgsql-sql@postgresql.org; Wed, 18 Mar 2015 12:15:19 +0000 Received: from mail-am1on0740.outbound.protection.outlook.com ([2a01:111:f400:fe00::740] helo=emea01-am1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYCsU-00088K-CQ for pgsql-sql@postgresql.org; Wed, 18 Mar 2015 12:15:16 +0000 Received: from AM3PR05MB418.eurprd05.prod.outlook.com (10.242.246.142) by AM3PR05MB418.eurprd05.prod.outlook.com (10.242.246.142) with Microsoft SMTP Server (TLS) id 15.1.112.19; Wed, 18 Mar 2015 12:08:46 +0000 Received: from AM3PR05MB418.eurprd05.prod.outlook.com ([10.242.246.142]) by AM3PR05MB418.eurprd05.prod.outlook.com ([10.242.246.142]) with mapi id 15.01.0112.000; Wed, 18 Mar 2015 12:08:46 +0000 From: Hector Vass To: Andreas Joseph Krogh , "pgsql-sql@postgresql.org" Subject: Re: Effective query for listing flags in use by messages in a folder Thread-Topic: [SQL] Effective query for listing flags in use by messages in a folder Thread-Index: AQHQYIZgPcF8AjH69E2ZKdgW2he4CZ0glMZwgACVnwCAAOfl5YAAEqqAgAACTTk= Date: Wed, 18 Mar 2015 12:08:45 +0000 Message-ID: <1426680525528.15871@metametrics.co.uk> References: <1426676819968.86115@metametrics.co.uk>, In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: yes X-MS-TNEF-Correlator: x-originating-ip: [81.149.172.36] authentication-results: visena.com; dkim=none (message not signed) header.d=none; x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:AM3PR05MB418; x-forefront-antispam-report: BMV:1; SFV:NSPM; SFS:(10019020)(2950100001)(106116001)(19627595001)(17760045003)(117636001)(46102003)(36756003)(19617315012)(16236675004)(19625215002)(19580395003)(19580405001)(122556002)(2656002)(2420400003)(15975445007)(102836002)(87936001)(18206015028)(74482002)(40100003)(92566002)(50986999)(99936001)(66066001)(86362001)(19627405001)(2501003)(76176999)(54356999)(77096005)(62966003)(107886001)(77156002); DIR:OUT; SFP:1102; SCL:1; SRVR:AM3PR05MB418; H:AM3PR05MB418.eurprd05.prod.outlook.com; FPR:; SPF:None; MLV:sfv; LANG:en; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:AM3PR05MB418; BCL:0; PCL:0; RULEID:; SRVR:AM3PR05MB418; x-forefront-prvs: 051900244E Content-Type: multipart/related; boundary="_004_142668052552815871metametricscouk_"; type="multipart/alternative" MIME-Version: 1.0 X-OriginatorOrg: metametrics.co.uk X-MS-Exchange-CrossTenant-originalarrivaltime: 18 Mar 2015 12:08:45.9494 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 0245920b-b0ee-44df-a137-423185636e7a X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM3PR05MB418 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_004_142668052552815871metametricscouk_ Content-Type: multipart/alternative; boundary="_000_142668052552815871metametricscouk_" --_000_142668052552815871metametricscouk_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable do you want to post some insert statements to populate the table message wi= th some realistic example data.. Hector Vass +44(0)7773 352 559 * Metametrics, International House, 107 Gloucester Road, Malmesbury, Wilt= shire, SN16 0AJ * www.metametrics.co.uk ________________________________ From: pgsql-sql-owner@postgresql.org on be= half of Andreas Joseph Krogh 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=E5 onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass >: Andreas ... your code and one of my examples ... I have modified my optio= n 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=3D3 with the text represent= ation of the flag) ... when you satisfy yourself this produces the same res= ults you might then want to go back and re-read my original post which rath= er 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 productio= n 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 re= quires 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 pr= oblem 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 t= hat 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 te= xt 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 www.visena.com [cid:part_F764646534471GSEI0S] --_000_142668052552815871metametricscouk_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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


Hector Vass




+44(0)7773 352 559

*  Metametrics, International House, 107 Glouc= ester Road,  Malmesbury, Wiltshire, SN16 0AJ

8   www.metam= etrics.co.uk

 

From: pgsql-sql-owner@postg= resql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Josep= h 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 messa= ges in a folder
 
P=E5 onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass <hector.vass@metametrics.co.uk= >:

Andreas  ... your code = and one of my examples ...  I have modified my option 2 to give an&nbs= p;example with data that gives you I believe exactly the sam= e output (one row for each flag set for folder_id=3D3 with the text representation of the flag) ... when you satisfy yourself this p= roduces 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 becau= se 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 t= he problem of listing a distinct set of flags being set on messages in a fo= lder. AFAICS your examples list messages with any or a specific set of flag= s 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 ea= ch message and a message may have several flags set.
 
This is a more realistic table, with "msg" as varchar holdin= g the actual text of the message:
 
cr=
eate table message=
(=0A=
    folder_id integer not NULL,=0A=
    m=
sg varchar NOT NULL,=0A=
    is_seen =
boolean NOT NULL defau=
lt false,=0A=
    is_replied boolean not null de=
fault false,=0A=
    is_forwarded boolean not null =
default false,=0A=
    is_deleted boolean not null de=
fault false,=0A=
    is_draft boolean not null defa=
ult false,=0A=
    is_flagged boolean not null de=
fault false=0A=
);
 
--
Andreas= Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47= 909 56 963
=3D""
 
--_000_142668052552815871metametricscouk_-- --_004_142668052552815871metametricscouk_ Content-Type: image/png; name="ATT00001.png" Content-Description: ATT00001.png Content-Disposition: inline; filename="ATT00001.png"; size=1913; creation-date="Wed, 18 Mar 2015 12:00:01 GMT"; modification-date="Wed, 18 Mar 2015 12:00:01 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= --_004_142668052552815871metametricscouk_--