From bricklen@gmail.com Thu Aug  20 20:33:07 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 703A7632D04
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 17:33:07 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 70045-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 20:32:57 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.27])
	by mail.postgresql.org (Postfix) with ESMTP id 6A118635300
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 17:32:57 -0300 (ADT)
Received: by qw-out-2122.google.com with SMTP id 3so107207qwe.61
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 13:32:55 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:date:message-id:subject
	:from:to:content-type;
	bh=B/hGQfoZkp4kA/goMc5jMSMTKsj0QjomKnoMolQSUuI=;
	b=qS8LuocNxQvvW4d/LdA2VA8hql59AwHVOCYTNLJHqp6Je0BDU7g4PSRo2GeNH7SPl0
	gOQuIRolYIwWnTBnZ9fA8IdHhMu7j3Pw+qHgRc+NIgPbEVGX8Hr1/Fk3gKQNQvFQNf/Z
	FlGh02xdjnrGE564uGjl3aCNknOvmKZd5FmkE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=BLarvXqDm3EjoQbM/JOiuH+/vG25C80O7m1FSTDyvAlrdbfVnTM9yCVCGKVB3tNAgk
	2gNCGE1QxO8visaz9I0CU8v14OSqCZGekVBU11WrrOK65ofEXO2aK2jxUV3UjrFsQa83
	nv+H9BjdQ9YRWV59LVwxQp6Mb7wUliCBYAXSk=
MIME-Version: 1.0
Received: by 10.229.54.78 with SMTP id p14mr45756qcg.67.1250800375018;
 Thu, 20
	Aug 2009 13:32:55 -0700 (PDT)
Date: Thu, 20 Aug 2009 13:32:54 -0700
Message-ID: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
Subject: Rewrite without correlated subqueries
From: bricklen <bricklen@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=00151773e6ba09b4f5047198a828
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5
	tests=BAYES_20=-0.74, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/114
X-Sequence-Number: 33023

--00151773e6ba09b4f5047198a828
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hi All,

I'm having some trouble wrapping my head around the syntax to rewrite a
query using correlated subqueries, to using outer joins etc.

The query:

SELECT  ps.userid,
               SUM( ps.hits ) as numhits
FROM primarystats AS ps
  INNER JOIN camp ON camp.id = ps.idcamp
  INNER JOIN sites ON sites.id = ps.idsite
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE
sitescategory.idsites = ps.idsites )
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE
sitescategory.idsites = ps.idsites )
GROUP BY ps.userid;

Because I am rewriting this query to use Greenplum, I cannot use correlated
subqueries (they are not currently supported).

Can anyone suggest a version that will garner the same results? I tried with
OUTER JOINS and some IS NULLs, but I couldn't get it right.

Thanks!

bricklen

--00151773e6ba09b4f5047198a828
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi All,<br><br>I&#39;m having some trouble wrapping my head around the synt=
ax to rewrite a query using correlated subqueries, to using outer joins etc=
.<br><br>The query:<br><br>SELECT=C2=A0 ps.userid,<br>=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SUM( ps.hit=
s ) as numhits<br>

FROM primarystats AS ps<br>=C2=A0 INNER JOIN camp ON <a href=3D"http://camp=
.id" target=3D"_blank">camp.id</a> =3D ps.idcamp<br>=C2=A0 INNER JOIN sites=
 ON <a href=3D"http://sites.id" target=3D"_blank">sites.id</a> =3D ps.idsit=
e<br>WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory =
WHERE sitescategory.idsites =3D ps.idsites )<br>

AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE=
 sitescategory.idsites =3D ps.idsites )<br>GROUP BY ps.userid;<br><br>Becau=
se I am rewriting this query to use Greenplum, I cannot use correlated subq=
ueries (they are not currently supported).<br>

<br>Can anyone suggest a version that will garner the same results? I tried=
 with OUTER JOINS and some IS NULLs, but I couldn&#39;t get it right.<br><b=
r>Thanks!<br><br>bricklen<br><br><br>

--00151773e6ba09b4f5047198a828--



From Mark.Fenbers@noaa.gov Thu Aug  20 20:41:07 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0728B632C52
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 17:41:07 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 71256-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 20:40:57 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mmp1.nems.noaa.gov (mmp1.nems.noaa.gov [140.90.121.156])
	by mail.postgresql.org (Postfix) with ESMTP id 0EADF632959
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 17:40:57 -0300 (ADT)
Received: from [198.206.42.50] by mmp1.nems.noaa.gov
	(Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005))
	with ESMTPSA id <0KOP00FZ10465000@mmp1.nems.noaa.gov> for
	pgsql-sql@postgresql.org; Thu, 20 Aug 2009 16:40:55 -0400 (EDT)
Date: Thu, 20 Aug 2009 16:40:55 -0400
From: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: Re: Rewrite without correlated subqueries
In-reply-to: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
To: bricklen <bricklen@gmail.com>
Cc: pgsql-sql@postgresql.org
Message-id: <4A8DB4D7.6030102@noaa.gov>
Organization: DOC/NOAA/NWS/OHRFC
MIME-version: 1.0
Content-type: text/html; charset=UTF-8
Content-transfer-encoding: 8BIT
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-5.141 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457,
	RCVD_IN_DNSWL_MED=-4
X-Spam-Level: 
X-Archive-Number: 200908/115
X-Sequence-Number: 33024

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Try putting your subqueries into temporary tables, first, inside a
BEGIN ... COMMIT block.  But your subqueries would produce the
negative, i.e., everything except where sitescategory.idsites =
ps.idsites.  Then reference these temp tables in your query with inner
or outer joins as appropriate.  Your new query would not include the
... IN ( &lt;list&gt; ) syntax...<br>
<br>
Mark<br>
<br>
bricklen wrote:
<blockquote
 cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com"
 type="cite">Hi All,<br>
  <br>
I'm having some trouble wrapping my head around the syntax to rewrite a
query using correlated subqueries, to using outer joins etc.<br>
  <br>
The query:<br>
  <br>
SELECT  ps.userid,<br>
               SUM( ps.hits ) as numhits<br>
FROM primarystats AS ps<br>
  INNER JOIN camp ON <a moz-do-not-send="true" href="http://camp.id"
 target="_blank">camp.id</a> = ps.idcamp<br>
  INNER JOIN sites ON <a moz-do-not-send="true" href="http://sites.id"
 target="_blank">sites.id</a> = ps.idsite<br>
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
GROUP BY ps.userid;<br>
  <br>
Because I am rewriting this query to use Greenplum, I cannot use
correlated subqueries (they are not currently supported).<br>
  <br>
Can anyone suggest a version that will garner the same results? I tried
with OUTER JOINS and some IS NULLs, but I couldn't get it right.<br>
  <br>
Thanks!<br>
  <br>
bricklen<br>
  <br>
  <br>
</blockquote>
</body>
</html>



From bricklen@gmail.com Thu Aug  20 21:16:17 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0F04A633F2E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 18:16:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 82177-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 21:16:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24])
	by mail.postgresql.org (Postfix) with ESMTP id D8C8B62E9C6
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 18:16:06 -0300 (ADT)
Received: by qw-out-2122.google.com with SMTP id 3so125437qwe.61
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 14:16:05 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=wpksBq5KYnrQtl18AoAGtAC0ZFic4N8O/Ui3ewMZ22U=;
	b=DrYRTW8h5S9TKVz5+UYhkmXxcDnP2jZF/5d/A3RYnEYMqeL/x4S41CNrpFUDWva1Fo
	Rs/fEbYyzQNryZPVePlKZp9auAJ2cebn+2wZ0SBijh9EEe7bJT1Ym+8W8iwVng2L02xT
	LeIffLT/k+1314eiOH2ol+ktUYJILquH5cYHI=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=QImHTnsBE6T/zxSU/ibjqqf5xS+iyO29fB3RQUY1s61ZMcN3Sv6qkrz9fJlBLRN3wP
	48ua8lDfql0YACOBR6Tf3Cd+VMQbfB/nEX41LOzQfWy45OvH+7AdldQF09Pv+Plvra+Z
	37Nfq4S3ovX0DasZhifUSOpknrVEccfluoxjI=
MIME-Version: 1.0
Received: by 10.229.55.69 with SMTP id t5mr57382qcg.34.1250802964701; Thu, 20
	Aug 2009 14:16:04 -0700 (PDT)
In-Reply-To: <4A8DB4D7.6030102@noaa.gov>
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
	<4A8DB4D7.6030102@noaa.gov>
Date: Thu, 20 Aug 2009 14:16:04 -0700
Message-ID: <33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.com>
Subject: Re: Rewrite without correlated subqueries
From: bricklen <bricklen@gmail.com>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0015175dd8b86524c504719942a8
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/116
X-Sequence-Number: 33025

--0015175dd8b86524c504719942a8
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Interesting idea. Preferably this operation could be done in straight SQL in
a single transaction, to fit in with the way our application works, but if
that's not possible I may need to go the temporary table route.

On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:

>  Try putting your subqueries into temporary tables, first, inside a BEGIN
> ... COMMIT block.  But your subqueries would produce the negative, i.e.,
> everything except where sitescategory.idsites = ps.idsites.  Then reference
> these temp tables in your query with inner or outer joins as appropriate.
> Your new query would not include the ... IN ( <list> ) syntax...
>
> Mark
>
>
> bricklen wrote:
>
> Hi All,
>
> I'm having some trouble wrapping my head around the syntax to rewrite a
> query using correlated subqueries, to using outer joins etc.
>
> The query:
>
> SELECT  ps.userid,
>                SUM( ps.hits ) as numhits
> FROM primarystats AS ps
>   INNER JOIN camp ON camp.id = ps.idcamp
>   INNER JOIN sites ON sites.id = ps.idsite
> WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> GROUP BY ps.userid;
>
> Because I am rewriting this query to use Greenplum, I cannot use correlated
> subqueries (they are not currently supported).
>
> Can anyone suggest a version that will garner the same results? I tried
> with OUTER JOINS and some IS NULLs, but I couldn't get it right.
>
> Thanks!
>
> bricklen
>
>
>

--0015175dd8b86524c504719942a8
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Interesting idea. Preferably this operation could be done in straight
SQL in a single transaction, to fit in with the way our application
works, but if that&#39;s not possible I may need to go the temporary table
route.<br><br><div class=3D"gmail_quote">On Thu, Aug 20, 2009 at 1:40 PM, M=
ark Fenbers <span dir=3D"ltr">&lt;<a href=3D"mailto:Mark.Fenbers@noaa.gov">=
Mark.Fenbers@noaa.gov</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_q=
uote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0=
pt 0.8ex; padding-left: 1ex;">



 =20

<div bgcolor=3D"#ffffff" text=3D"#000000">
Try putting your subqueries into temporary tables, first, inside a
BEGIN ... COMMIT block.=C2=A0 But your subqueries would produce the
negative, i.e., everything except where sitescategory.idsites =3D
ps.idsites.=C2=A0 Then reference these temp tables in your query with inner
or outer joins as appropriate.=C2=A0 Your new query would not include the
... IN ( &lt;list&gt; ) syntax...<br><font color=3D"#888888">
<br>
Mark</font><div><div></div><div class=3D"h5"><br>
<br>
bricklen wrote:
<blockquote type=3D"cite">Hi All,<br>
  <br>
I&#39;m having some trouble wrapping my head around the syntax to rewrite a
query using correlated subqueries, to using outer joins etc.<br>
  <br>
The query:<br>
  <br>
SELECT=C2=A0 ps.userid,<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 SUM( ps.hits ) as numhits<br>
FROM primarystats AS ps<br>
=C2=A0 INNER JOIN camp ON <a href=3D"http://camp.id" target=3D"_blank">camp=
.id</a> =3D ps.idcamp<br>
=C2=A0 INNER JOIN sites ON <a href=3D"http://sites.id" target=3D"_blank">si=
tes.id</a> =3D ps.idsite<br>
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites =3D ps.idsites )<br>
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites =3D ps.idsites )<br>
GROUP BY ps.userid;<br>
  <br>
Because I am rewriting this query to use Greenplum, I cannot use
correlated subqueries (they are not currently supported).<br>
  <br>
Can anyone suggest a version that will garner the same results? I tried
with OUTER JOINS and some IS NULLs, but I couldn&#39;t get it right.<br>
  <br>
Thanks!<br>
  <br>
bricklen<br>
  <br>
  <br>
</blockquote>
</div></div></div>

</blockquote></div><br>

--0015175dd8b86524c504719942a8--



From scott.marlowe@gmail.com Thu Aug  20 21:59:32 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D2BB463518D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 18:59:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 90191-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 21:59:22 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f216.google.com (mail-ew0-f216.google.com
	[209.85.219.216])
	by mail.postgresql.org (Postfix) with ESMTP id 7CD006350FE
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 18:59:22 -0300 (ADT)
Received: by ewy12 with SMTP id 12so262720ewy.24
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 14:59:20 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=coDilTyBbQJC8F6Ua9HmHIS7t24DjV+YFgV3OeCYAVw=;
	b=bxag1gjjrYx8NFc6hbXeGxYrJPLM5vHWGPPUhLVLY197vQ8jJGepHmUOgEMe/1wY+i
	PUpQhPn0GYp+NOBQylC0IIk8fWm9PxDgcs9Zf+IuZ7rxpzulyoL1jPaNFwuEhFHI7O4y
	oe8XdCOOG07Io64Cp/V3QPgd97DijpC0OapcU=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=P6ARhcskzwOEakaxVgLlFStJiVEfIRBiyEAAVddwW0nNlavc2IW6c2HWLQG4i+8PAX
	05iF84fOj92KZcX+1G+UXhYgDtuk37pkP4VcUgtc6c6i9o8SahNhubuWo9oNMYrgRj+O
	Ej5kTfEWGVCYlFfQC5EYvia6ppLGnYlfViHm4=
MIME-Version: 1.0
Received: by 10.211.148.2 with SMTP id a2mr331163ebo.68.1250805560633;
 Thu, 20
	Aug 2009 14:59:20 -0700 (PDT)
In-Reply-To: <33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.com>
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
	<4A8DB4D7.6030102@noaa.gov>
	<33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.com>
Date: Thu, 20 Aug 2009 15:59:20 -0600
Message-ID: <dcc563d10908201459l3f0be8c7wfffcb103fd79545b@mail.gmail.com>
Subject: Re: Rewrite without correlated subqueries
From: Scott Marlowe <scott.marlowe@gmail.com>
To: bricklen <bricklen@gmail.com>
Cc: Mark Fenbers <Mark.Fenbers@noaa.gov>, pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/117
X-Sequence-Number: 33026

On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote:
> Interesting idea. Preferably this operation could be done in straight SQL in
> a single transaction, to fit in with the way our application works, but if
> that's not possible I may need to go the temporary table route.

Temp tables can be included in a transaction, and they're not visible
to other connections.



From bricklen@gmail.com Thu Aug  20 22:03:27 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3F8E2634821
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 19:03:27 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 90937-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 22:03:17 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.27])
	by mail.postgresql.org (Postfix) with ESMTP id 7182F633722
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 19:03:17 -0300 (ADT)
Received: by qw-out-2122.google.com with SMTP id 3so144050qwe.61
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 15:03:16 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=Fgliy/BEVNorN/jR3VXmWczpKUpSyxGl1zlWrak5afQ=;
	b=vCW28r8ackSUArcGj8nWCUXU0MHl5OLmiHDpPu0hUXimJ1NJBk1Ev1ygjEtpL7fWlR
	KJurBmTpjWKvkf1ahdLIl4AhseCFiO1Rf74da8GLJLkIbNKXO1q8khsUG/6DDREYTdGA
	OcKK42SbkIwBYeaJgRT4RiCZjSXkuzkX1x5FQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=dZVHWR1x/qhgJVk4673zeWY7GN6Iu+kRdEXG8WbMc4be2HVRuzf5rg7zJ9FNP6PrQJ
	r4jMiYftBugrVZwbSWcHnGAQbGPHsmIAWl+y1Liz/xhVudCCpNbkl0HpA4EP+xQrerw7
	KnK0MQOIMWl+CoNhKiV5ASTOPR/0RqmPattLE=
MIME-Version: 1.0
Received: by 10.229.9.130 with SMTP id l2mr64583qcl.41.1250805796020; Thu, 20
	Aug 2009 15:03:16 -0700 (PDT)
In-Reply-To: <dcc563d10908201459l3f0be8c7wfffcb103fd79545b@mail.gmail.com>
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
	<4A8DB4D7.6030102@noaa.gov>
	<33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.com>
	<dcc563d10908201459l3f0be8c7wfffcb103fd79545b@mail.gmail.com>
Date: Thu, 20 Aug 2009 15:03:15 -0700
Message-ID: <33b743250908201503r10686639s17adc292565c5ee7@mail.gmail.com>
Subject: Re: Rewrite without correlated subqueries
From: bricklen <bricklen@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=00163683269c27a229047199eb96
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/118
X-Sequence-Number: 33027

--00163683269c27a229047199eb96
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

> On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote:
> > Interesting idea. Preferably this operation could be done in straight SQL
> in
> > a single transaction, to fit in with the way our application works, but
> if
> > that's not possible I may need to go the temporary table route.
>
> Temp tables can be included in a transaction, and they're not visible
> to other connections.
>

Yeah I know, but I was thinking more of replacing this query with vanilla
SQL. Maybe that's just not be feasible.

--00163683269c27a229047199eb96
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe <=
span dir=3D"ltr">&lt;<a href=3D"mailto:scott.marlowe@gmail.com">scott.marlo=
we@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" sty=
le=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;=
 padding-left: 1ex;">
<div class=3D"im">On Thu, Aug 20, 2009 at 3:16 PM, bricklen&lt;<a href=3D"m=
ailto:bricklen@gmail.com">bricklen@gmail.com</a>&gt; wrote:<br>
&gt; Interesting idea. Preferably this operation could be done in straight =
SQL in<br>
&gt; a single transaction, to fit in with the way our application works, bu=
t if<br>
&gt; that&#39;s not possible I may need to go the temporary table route.<br=
>
<br>
</div>Temp tables can be included in a transaction, and they&#39;re not vis=
ible<br>
to other connections.<br>
</blockquote></div><br>Yeah I know, but I was thinking more of replacing th=
is query with vanilla SQL. Maybe that&#39;s just not be feasible.<br><br><b=
r>

--00163683269c27a229047199eb96--