Saluton,
from the following passage in the man page on create index:
,----[ man l create_index ]
| CREATE [ UNIQUE ] INDEX index_name ON table
| [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
| ...
| In the second syntax shown above, an index is defined on
| the result of a user-specified function func_name applied
| to one or more attributes of a single class. These func
| tional indices can be used to obtain fast access to data
| based on operators that would normally require some trans
| formation to apply them to the base data.
`----
I get the impression that I can use an index on any function to speed
up access to that particular function, e.g. if I want to match strings
against a certain standardized form. Now consider a function like
,----
| create function whatever(int4) returns text as '
| declare
| r record;
| begin
| select data into r
| from otherTable where otherTable.id = $1
| order by whatever limit 1;
| return r.data;
| end;' language 'plpgsql';
`----
I might be tempted to have an index on whatever(firstTable.intField)
in order to be able to return this as fast as possible. But as the
function result obviously depends on data in a different table -
otherTable in the above function -, I wonder (a) how the index code
might figure out that a change to otherTable might trigger a change in
the function results and (b) whether maintaining such an index would
not in fact be a very tedious (and, consequently, slow) task.
So I guess it there must be some restriction on the legal functions
for such a construction, but I cannot find anything in the
documentation. Or am I simply wrong?
Albert.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
