Tom Lane <tgl@sss.pgh.pa.us> wrote:
[snip]
>
> The sub-select has its own aggregation pipeline that acts before the
> outer select does anything, so the basic rule of "no aggregate
> references in WHERE" is not being violated here.
>
I was thinking of an related thing, how do we handle queries like these
(actual used query):
select
o.id as order_id,
o.cust_id,
i.id as invoice_id,
i.created::date as invoice_created,
extract('days' from (now() - i.created)) as days_overdue,
c.type,
c.status
from
order o,
invoice i,
cust c
where
(o.ordersystem = 0) and
(o.status = 3 and o.substatus = 3) and
(i.order_id = o.id) and
(c.id = o.cust_id) and
(c.account_expires >= now()) and
((c.type & (1|4|8)::int8) = 0) and /* some int8 flags */
((c.status & (2|4)::int8) = 0) and /* some other int8 flags */
->extract('days' from (now() - i.created)) >= 20
order by
dagar_overdue desc
;
Is the days_overdue calculated twice, if it is, how can i get the effect
of replacing the where condition with days_overdue? Like:
select
days_overdue
...
where
(extract('days' from (now() - i.created)) AS days_overdue) >= 20
Hmm. Well it's not that big of an hassle but it'd look nice!
Not sure of how big of a performance win it would be, the extract thing
shouldn't be that slow, right?
Regards
Magnus Naeslund
