From: Aaron Bono <postgresql@aranya.com>
To: pgsql-sql@postgresql.org
Subject: Fwd: refining view using temp tables
Date: Mon, 6 Nov 2006 16:51:50 -0600
Message-ID: <bf05e51c0611061451w521e6d4dxd83a55ddea9dfd52@mail.gmail.com> (raw)
In-Reply-To: <bf05e51c0611061451g723b17e7y959ae8423f8237e7@mail.gmail.com>
References: <7097897.post@talk.nabble.com>
<bf05e51c0611061451g723b17e7y959ae8423f8237e7@mail.gmail.com>
---------- Forwarded message ----------
From: Aaron Bono <aaron.bono@aranya.com>
Date: Nov 6, 2006 4:51 PM
Subject: Re: [SQL] refining view using temp tables
To: BeemerBiker <jstateson@swri.edu>
Cc: pgsql-sql@postgresql.org
On 10/31/06, BeemerBiker <jstateson@swri.edu> wrote:
>
>
> Using postgre with dotnet VS8. I came up with a scheme of having a web
> user
> search thru the database and selecting into a temp table. Then a further
> "refined" search would use the temp table as input and another temp table
> as
> output. Then swap the source and destination so as to not use up
> resources.
> Maybe this is not a good mechanism. I found right away I could not easily
> check for table existence nor drop non-existent tables without getting a
> server error (even with try {} catch {}).
>
> I may have the same user bring up two or more pages during the same
> session.
> Ditto for other users. I would not want to code up temporary names using
> timestamps and usersnames if I could avoid it. It would be nice if the
> sql
> engine would handle this for me. Can someone suggest how postgresql
> could
> handle a google like "search within results".
The best approach will probably vary depending on the volume of usage on
your site.
One thing I would try is to create a table for searches and search results
with a primary key equal to the user's session ID or log in ID. This would
only give them one search but if you need more you can use a serogate key.
Then have everyone use the same table - no temp tables needed.
Table:
user_search
user_search_id (PK),
session_id (indexed),
create_dt,
last_access_dt
user_search_param (one record for each search parameter they entered for the
filter)
user_search_param_id (PK),
user_search_id (FK),
param_name,
param_value
user_search_results (one record per record returned in search)
user_search_results_id (PK - this may not be necessary),
user_search_id (FK),
sort_index (to help preserve sort order),
values (this can be CSV, XML or even broken into another table)
Then you can add a process that regularly deletes searches who's last access
date is older than so many minutes (cascade delete that is). Or you can add
a trigger that fires off this clean up whenever a new search is added.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com
Subject: Re: Fwd: refining view using temp tables
In-Reply-To: <bf05e51c0611061451w521e6d4dxd83a55ddea9dfd52@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox