Skip site navigation (1) Skip section navigation (2)

Re: VIEWs with aggregate functions

From: Paul Makepeace <postgresql(dot)org(at)paulm(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: VIEWs with aggregate functions
Date: 2004-10-19 12:12:27
Message-ID: 20041019121227.GB22444@mythix.realprogrammers.com (view raw or flat)
Thread:
Lists: pgsql-novice
Je 2004-10-14 05:09:16 +0100, Tom Lane skribis:
> 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.

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/

In response to

pgsql-novice by date

Next:From: Paul MakepeaceDate: 2004-10-19 12:18:48
Subject: Finding last day in a month
Previous:From: Martin FosterDate: 2004-10-19 02:43:31
Subject: Administrative lockout

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group