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=M2/6Mwb/MPLEISaCX5zqpv+tEZeHRqaolnYkHOiQphlgrbfjucbG3HPIkM9VMDYZkS
	cf3d7O6yoQDIjuigCZkfzPp0zNi0Icg8CU00oiuu6114utlKW7qSY2NWzJKo0m3/D1Vg
	1GE/JHu5hquM1ax3AfKMHplukzkqbfaUr2st8=
MIME-Version: 1.0
In-Reply-To: <20091222133527.3007bd96@dawn.webthatworks.it>
References: <20091222133527.3007bd96@dawn.webthatworks.it>
Date: Tue, 22 Dec 2009 20:47:18 +0100
Message-ID: <162867790912221147r7e3041fej44332a05faf915d6@mail.gmail.com>
Subject: Re: short-cutting if sum()>constant
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Ivan Sergio Borgonovo <mail@webthatworks.it>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8

Hello

I found one ugly trick. You can multiply lines and SUM > cons could be
replaced limit clause:

postgres=# select * from data;
 a
---
 3
 2
 1
 4
 2
 3
(6 rows)

Then SELECT * FROM WHERE and stop when SUM(a) = n

then

postgres=# select generate_series(1,a) from data;
 generate_series
-----------------
               1
               2
               3
               1
               2
               1
               1
               2
               3
               4
               1
               2
               1
               2
               3

So If I would to check if there are sum(a) >= 10 then I can use LIMIT
10. If query returns ten rows, then result is true, else result is
false

select a, (a = generate_series(1,a))::int from data limit 12; -- stop
after sum(a) = 12

postgres=# select sum(x) from (select 1 as x,(a =
generate_series(1,a))::int from data limit 12) s;
 sum
-----
  12 --              12 is eq 12, so test is successful

(1 row)


Regards
Pavel Stehule

2009/12/22 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> Hi,
>
> I'd like to know if
>
> select sum(qty) from t where status=37;
>
> is > constant.
>
> qty is always >0.
>
> Is there a way to skip examining further rows and return a result
> ASAP?
>
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>