Re: Storing computed values

Richard Broersma wrote:
> On Mon, Apr 21, 2008 at 10:34 AM, Colin Wetherbee <cww@denterprises.org> wrote:
>
>> I would like to have a table that contains a connection for each distinct
>> pair of points (point1 to point2 is the same as point2 to point1).  This
>> table would then be automatically updated every time a modification is made
>> to the reference table.  If my calculation is correct, the new table would
>> contain 3,654,456 rows using the current data set.
>>
>> I realize I could use a TRIGGER to keep the connections table fresh, and
>> perhaps that's also a solution.
>>
>> But, really, I'm just wondering if PostgreSQL has some automated, built-in
>> facility for situations like this?
>
> Would a functional index do this for you?  Perhaps, you wouldn't even
> need a table is you store these computed values in an index instead.

I'm not sure, as I've never used one before.  I think I briefly
considered it a while back and decided it wouldn't do what I want
because I don't know the value of the connection before-hand.  Perhaps
you can steer me in the right direction.

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).

Right now, in order to get my connection, I'm using something like:

SELECT connect(p_start.location, p_end.location)
FROM foo
JOIN points AS p_start ON foo.point_id_start = points.point_id
JOIN points AS p_end   ON foo.point_id_end   = points.point_id
WHERE foo.id = 8192;

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?

As I mentioned above, I don't know the result of connect() before the
query; that's what I'm trying to compute, not what I'm trying to search
against.

Thanks.

Colin