From: | "Magnus Naeslund(f)" <mag(at)fbab(dot)net> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Using an ALIAS in WHERE clause |
Date: | 2002-11-29 01:46:20 |
Message-ID: | 06c001c29749$1dc03de0$f80c0a0a@mnd |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | CN | 2002-11-29 01:54:24 | Re: Server v7.3RC2 Dies |
Previous Message | Justin Clift | 2002-11-29 01:44:15 | Re: PostgreSQL Global Development Group Announces Version 7.3 |