Re: how do functional indices work?

hubert depesz lubaczewski <depesz(at)depesz(dot)pl> writes:
> let's assume i have table users which is (id int4, person_id int4) - pkey'ed
> on id with index on person_id.
> next i have table people (id int4, fullname text) with pkey on id.
> there is a foreign key between the two tables on users.person_id => people.id.
> now i wrote a function, which given user id returns it's person's name. quite
> simple function.
> not i want to make a index:
> create index test on users (myMagicalFunction(id));
> this of course works,

No, it doesn't. A functional index using a function that depends on any
data other than its explicitly passed parameters is a horribly bad idea.
It WILL fail --- nastily --- as soon as you change the other table.

To help catch this, 7.2 will not allow you to build functional indexes
on functions that are not marked "iscachable".

regards, tom lane