Re: Storing computed values

Colin Wetherbee wrote:
> 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?

I think I now see where you're going with this, but this makes my
problem somewhat more interesting.

I can imagine two ways of solving this.

First, an INDEX with a JOIN... (I can see Tom Lane laughing at me now.)

CREATE INDEX foo_connect_idx
ON foo connect(p_start.location, p_end.location)
JOIN points AS p_start ON foo.point_id_start = p_start.point_id
JOIN points AS p_end ON foo.point_id_end = p_end.point_id;

Just in case this might work, I checked the documentation and found no
mention of JOIN anywhere in the INDEX sections.

Second, bury the JOIN part in my PL/Perl function and use spi_ functions
to retrieve my actual PostGIS locations so the process is transparent to
CREATE INDEX.

Hmm.

Colin