Re: Index on function referring other table

"Albert REINER" <areiner(at)tph(dot)tuwien(dot)ac(dot)at> writes:
> 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.

The answer is that the system takes no account of any such thing.
Therefore an index function that depends on any data other than the
presented arguments is a dangerous animal.

I wouldn't want to see the system try to forbid this sort of thing,
because I can see uses for it, *as long as you don't change the
reference table* (or, perhaps, drop and rebuild the index when you do).
But you'd better keep in mind that sharp tools can injure careless users.

> 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?

The documentation probably fails to mention that :-(

regards, tom lane