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
