I think this is a common task, but I'm not sure how to do it.
I want to run a query that can return many records, display them 10 at a time, and be able to go forward/backward in the list. I'm not concerned about the list changing after the initial query.
I'm accessing this via a php web page. I'm thinking that maybe the best way to do this, other than re-running the query each time, is to put the results into a temporary table. I think this will work if I never call "disconnect" from the php script. My question is, when does my Postgres session end? Is there a timeout?
Of course, if this is a really bad way to do this, I'm willing to learn a better way!
Unfortunately, you can't use a temporary table - each webpage request will generate a new connection. If you are using php's permanent connections that still doesn't help you since you'll have several connections and can't guarantee which one is reused.
So - the simplest way is to use a permanent table search_results_cache and store your results in there with some sort of session_code. Then, you can step through that and delete the results when the session is over. You might store just the IDs required to reconstitute the results, depending on how long it takes to calculate these.
Check the mailing list archives for details - there was a length discussion recently.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
