Re: VIEWs with aggregate functions

Je 2004-10-14 05:09:16 +0100, Tom Lane skribis:
> Paul Makepeace <postgresql.org@paulm.com> writes:
> >   CREATE VIEW weekly_bar_info AS
> >      SELECT bar_name,
> >             MIN(bar_date) AS earliest_date, MAX(bar_date) AS latest_date,
> >             MIN(bar) as min_bar, MAX(bar) as max_bar
> >        FROM bar_data
> >       WHERE valid_weekly='t'
> >    GROUP BY bar_name
>
> > What I'd like to be able do is WHERE bar_name = ? AND bar_date >= ?
> > i.e. select over a date range but that isn't directly possible with the
> > VIEW as is.
>
> You can certainly select on bar_name, but you can't select on bar_date
> because the view doesn't expose bar_date, only some statistics about
> it.  I suppose that you want the WHERE clause to filter the bar_data
> rows before the aggregation happens (is that correct??) and you just
> can't do that with a view.

Yes, that's right, that is indeed what I was after.

>                             If it worked like that then the WHERE clause
> would not simply limit the set of rows returned by the view but actually
> change some of the values in those rows, and that's totally contrary to
> the SQL worldview.
>
> I think you're stuck with writing it out as one big query :-(

OK thanks for the confirmation - I suspected that was the case but
wanted to be sure!

Cheers, Paul

>             regards, tom lane

--
Paul Makepeace .............................. http://paulm.com/inchoate/

"What is the origin of happiness? Badly spelt."
   -- http://paulm.com/toys/surrealism/