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

Re: VIEWs with aggregate functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Paul Makepeace <postgresql(dot)org(at)paulm(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: VIEWs with aggregate functions
Date: 2004-10-14 04:09:16
Message-ID: 25293.1097726956@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Kumar SDate: 2004-10-14 15:18:42
Subject: Two table joins
Previous:From: Hari BhanujanDate: 2004-10-14 03:40:06
Subject: Re: possible to access a Linux box db from a Windows box

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