Re: Using an ALIAS in WHERE clause

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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