Re: VIEWs with aggregate functions

Paul Makepeace <postgresql(dot)org(at)paulm(dot)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. 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 :-(

regards, tom lane