From: | Dirk Niggemann <dirk-n(at)dircon(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)hub(dot)org |
Subject: | Functional indices... |
Date: | 2000-02-23 00:04:34 |
Message-ID: | 38B32412.6F54EB9F@dircon.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)dircon(dot)co(dot)uk
From | Date | Subject | |
---|---|---|---|
Next Message | Dustin Sallings | 2000-02-23 03:14:16 | Re: [GENERAL] Re: [General] pgsql on win95 |
Previous Message | CAMPZ | 2000-02-22 22:31:43 | Help w/procedures |