Functional indices...

Hello,

After some experimentation in pgsql, I would like to understand the
following:
1. Why can't functions for functional indices be defined in plpgsql
  (nothing stops you from  doing this in pltcl)?
2. Have I missed something, or are constants and constant expressions
not supoported in mutiple-argument lists to functions specified for
functional indices?
3. Is there any reason why no attempt is made to use the precomputed
value in the functional index on a table during a select from the table
not contrained by a where clause on the precomputed function value, or
is this impossible (i.e. functional index allows forward mapping: func
outut value->input column value/location  but not backward mapping input
column value/location->func value).
4. Can functional indices be used to optimise group by clauses in any
way- they only seem to come into effect on a where constraint prior to
the group by... (It  appeared to me they might be usable for eliminating
intermediate sorts on group by clauses- maybe i've missed something
here. )

why does this:

select sysno, myfunc(logstamp) as hour, sum(totalcalls) as
tcalls_period, sum (calls) as calls from stats_system where line>0  and
myfunc(logstamp) = 23 group by sysno, hour

work

but this

select sysno, myfunc(logstamp) as hour, sum(totalcalls) as
tcalls_period, sum (calls) as calls from stats_system where line>0  and
hour = 23 group by sysno, hour

give the error

ERROR:  attribute 'hour' not found

(Is this some misunderstaning of the syntax of column aliases in SQL on
my part?)

BTW this is all postgresql 6.5.2

Thanks,

Dirk


        Dirk Niggemann
      dirk-n@dircon.co.uk