Re: temp tables ORACLE/PGSQL

I am assuming you need "session varables" for a web based app right?

For a standard client/server app created in something like VB or Delphi all you really need is a single
connection(because most db apps are single threaded), and a temp table will stay around until that
connection is closed, and if you use ON COMMIT DELETE ROWS you don't ever need to
use execute in functions(that was the whole point).

You are right that the temp tables would not work with HTTP since each request is a new connection,
but there are easy ways around that by using a sessions table, which sounds like what you did.
For storing web based session variables I use a table in PG like this:
CREATE TABLE public.sessions
(
sessionid integer,
sesstimestamp timestamp,
sessdata text,
CONSTRAINT pk_sessions PRIMARY KEY (sessionid)
);

sessionID is generated from a sequence and stored on the client in a cookie
sesstimestamp is used to expire the session after 20 minutes of no activity
(the timestamp is updated at each new request from the client, so it's not a hard 20 minutes)
sessdata can contain as many variables as you like in this form:
username=bsmith

It all works great and I don't need global temp tables :-) And using a scheme like this will work on
any database that supports sequences or some other form of generating a unique ID.

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

fisher wrote:
> Hi
> Thank You very much.
> As I mentioned I need temp tables for storing "sesssion variables".
> I plan to write functions to return suitable column value and I need
> them to be availabele during whole session. That makes deleteing on
> commit not the best solution. For example I want to keep emp_id in one
> of columns and
> use ps_get_emp_id() function to return it's value in other functions.
> All values stored in thie "parameter" temp table are quite stable.
>
> Anyway thank You very much.
> fisher
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>