I'm probably missing something obvious here but I can't quite see it.
Given,

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.

Is there some way of achieving this?

(The reason for the view is that that set of aggregate functions is
appearing a few times in related queries, and that perl's Class:BI is
less of a headache with VIEWs.)

Paul


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

"If they asked us nicely, then we wouldnt be as happy with it."
-- http://paulm.com/toys/surrealism/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Paul Makepeace wrote:

earliest_date >= ?

or elimate the view and query directly from the table:

SELECT bar_name,
bar_date,
MIN(bar) as min_bar, MAX(bar) as max_bar
FROM bar_data
WHERE valid_weekly='t' AND bar_name = ? AND bar_date BETWEEN ? AND ?
GROUP BY bar_name


Ron


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Paul Makepeace <postgresql.org (AT) paulm (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Je 2004-10-14 05:09:16 +0100, Tom Lane skribis:
Yes, that's right, that is indeed what I was after.

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

Cheers, Paul

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster