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
