Re: Storing computed values

Richard Broersma wrote:
> On Mon, Apr 21, 2008 at 11:02 AM, Colin Wetherbee <cww(at)denterprises(dot)org> wrote:
>
>> Let's say my points table looks like this:
>>
>> point_id | location
>> ---------+----------
>> 1 | 010100000000... <-- some PostGIS geometry string
>> 2 | 010100000000...
>>
>> And, my foo table, which contains data pertaining to these connections,
>> looks like this:
>>
>> id | point_id_start | point_id_end
>> ---+----------------+--------------
>> 1 | 1 | 2
>>
>> And, let's say my function is connect(location1, location2).
>
>> I would like to be able to retrieve that connection without using the
>> connect() procedure. How would I be able to take advantage of a functional
>> index in this context?
>
> I am not sure what kind of constraints you have on your points table
> for location. It location is unique, this might be an example where
> you can solve your problem if you use a natural foreign key in foo
> instead of a surrogate key.

Yes, every location is currently unique, and I can't think of a
situation where I would have useful duplicates.

> Then you could just create an index:
>
> CREATE INDEX ON Foo Connect( location1, location2);

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?

Colin