Re: [HACKERS] [6.5.3] 'attribute not found'

The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> explain SELECT w.counter_id,
> ( date_part('month', w.stat_date) || '/' ||
> date_part('day', w.stat_date) || '/' ||
> date_part('year', w.stat_date) || ' 00:00:00')::datetime as
> dayperiod,
> r.referrer_id, count(w.referrer_hits)
> FROM webhit_referer_raw w, referrer_data r, referrer_link l
> WHERE w.stat_date < 'Jan 25 2000'
> AND w.referrer_url = r.referrer
> AND l.counter_id = w.counter_id
> AND l.stat_date = dayperiod
> AND l.referrer_id = r.referrer_id
> GROUP BY dayperiod, counter_id, referrer_id
> ORDER BY counter_id, dayperiod;

> I get:
> ERROR: attribute 'dayperiod' not found

> I'm guessing right now its on: "AND l.stat_date = dayperiod"

Yup.

> Should that not work?

No, it shouldn't. Labeling SELECT output columns with AS doesn't
affect the namespace visible in the WHERE clause (since WHERE is
"upstream" of the SELECT outputs). If we had sub-selects in FROM,
I believe that AS would determine the column names seen outside
the sub-select ("downstream").

Also, you can use the AS names in ORDER BY, which is also "downstream"
of forming the results. (I think we currently accept them in GROUP BY
as well, but I suspect that that is not in compliance with the
standard...)

regards, tom lane