Richard Broersma wrote:
> On Mon, Apr 21, 2008 at 11:19 AM, Colin Wetherbee <cww@denterprises.org> wrote:
>
>> My impression of functional indexes is that they're useful mostly in WHERE
>> queries, like the following.
>>
>> SELECT foo, bar, baz FROM some_table WHERE lower(foo) = 'qux';
>>
>> In this case, the index would be created on lower(foo).
>>
>> How would I get the value of the functional index out of the index in my
>> case?
>
> If this is the case, and you use Natural Foreign Keys, you only need
> to select from your foo table:
>
> SELECT Connect( location1, location2)
> FROM Foo
> WHERE location1 = ...
> OR location2 = ...
> OR connect(location1,location2) = ...; --this is where the
> functional index would be useful
Right, but the problem I see is that my locations are not actually
stored in foo. Since many rows of foo can reference the same location,
the locations are stored in a separate table and, in fact, are
referenced by foreign keys (SERIAL/INTEGER types) already.
Colin