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@zmt.uni-bremen.de>
              Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------