From: | Colin Wetherbee <cww(at)denterprises(dot)org> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing computed values |
Date: | 2008-04-21 18:02:45 |
Message-ID: | 480CD6C5.1090909@denterprises.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Richard Broersma wrote:
> On Mon, Apr 21, 2008 at 10:34 AM, Colin Wetherbee <cww(at)denterprises(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Colin Wetherbee | 2008-04-21 18:08:23 | Re: Storing computed values |
Previous Message | Richard Broersma | 2008-04-21 17:48:50 | Re: Storing computed values |