dificulties with views and aggregates

Hello out there,

There is a line "Allow DISTINCT on views" in the TODO of 6.5.3; I
think my problem below is due to this ... is there a workaround other
than creating a "real" table instead of a view? Is there any
"schedule" for the "view"-problems (just an interested question, by no
means intended to be offensive! I'm quite happy with postgreSQL so
far :-)

Thank you,
Ulf

P.S.: please CC: me in case of an answer, I'm not on the list at the
moment!

----------------------------------------------------------------------
My problem is: I have a table with a date and a time column
combined with a measurement value.

db=> create table test (d date, t time, val float8);
CREATE
[... insert something ...]

Now I want to calculate the mean daily maximal span of the recorded
values for each month. I create a view:

db=> create view span as
db-> select date_part('year', d) as yr,
db-> date_part('month', d) as mon,
db-> max(val)-min(val) as vdiff
db-> from test group by d;
CREATE

Now I try to calculate the average span:

db=> select yr, mon, avg(vdiff)
db-> from span group by yr, mon order by 1, 2;

yr mon avg
----+---+------------------
1997 1 1.62315789473684
1997 1 3.17684210526316
1997 1 2.66842105263158
[...]

There should be only one tupel for each year/month combination
returned. I is also not possible to select e.g. yr "distict":

db=> select distinct yr from span;

yr
----
1997
1997
1997

If there is only a "date_part()"-column in the view, "distinct" works:

db=> create view dpt as
db-> select date_part('month', d) as mon from test;
CREATE
db=> select distinct * from dpt;
mon
---
1
2
3

--
======================================================================
Ulf Mehlig <umehlig(at)zmt(dot)uni-bremen(dot)de>
Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------