Richard Broersma wrote:
On Mon, Apr 21, 2008 at 11:19 AM, Colin Wetherbee <cww@xxxxxxxxxxxxxxxx> 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
