Re: Using an ALIAS in WHERE clause

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