Re: Storing computed values

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.

Then you could just create an index:

CREATE INDEX ON Foo Connect( location1, location2);

--
Regards,
Richard Broersma Jr.