Re: Creating a session variable in Postgres

Thx guys Both Solutions works fine for me but which one is better (uses less
resources ?) ?
(Mike i tested your solution)

The use of sequence would require to clean up the table every N hour .
The use of temporary table wouldn't require any cleanup. Plus it won't use
any disk space (i suppose a temporary table wouldn't be written to disk
right ?).

Thx guys for your help.


----- Original Message -----
From: "Mike Mascari" <mascarm@mascari.com>
To: "Manfred Koizar" <mkoi-pg@aon.at>
Cc: "Nagib Abi Fadel" <nagib.abi-fadel@usj.edu.lb>; "Bruce Momjian"
<pgman@candle.pha.pa.us>; "generalpost" <pgsql-general@postgresql.org>
Sent: Thursday, June 03, 2004 04:55 PM
Subject: Re: [GENERAL] Creating a session variable in Postgres


> Manfred Koizar wrote:
> > On Thu, 3 Jun 2004 09:04:43 +0200, "Nagib Abi Fadel"
> > <nagib.abi-fadel@usj.edu.lb> wrote:
> >
> >>Let's say for example the variable is called "X". The view is called
> >>"t_view" and the temporary table is called "t_temp".
> >>Each time a user connects to the web, the application will initialize
the
> >>variable X and it will be inserted into the temporary table t_temp.
> >
> > Sequence values are session-specific which is exactly the property
> > you're looking for.
> >
> > CREATE TABLE session (
> > id SERIAL PRIMARY KEY,
> > x text
> > );
> >
> > CREATE VIEW mysession (x) AS
> > SELECT x FROM session WHERE id=currval('session_id_seq');
> >
> > CREATE VIEW t_view AS
> > SELECT *
> >   FROM SomeTable st INNER JOIN mysession s
> >     ON st.id = s.x;
> >
> > At the start of a session you just
> >
> > INSERT INTO session (x) VALUES ('whatever');
>
> Couldn't one also do (this is untested - may include syntax errors):
>
> -- Create a wrapper function for View usage
>
> CREATE FUNCTION getValue() RETURNS text AS '
>
> DECLARE
>
> result text;
>
> BEGIN
>
> SELECT INTO result session_value
> FROM session_data;
>
> RETURN result;
>
> END;
>
> LANGUAGE 'plpgsql';
>
> -- Create our View using our function
>
> CREATE VIEW t_view AS
> SELECT *
> FROM foo
> WHERE foo.key = getValue();
>
> -- Now, upon connecting, the app does:
>
> CREATE TEMPORARY TABLE session_data (value text);
> INSERT INTO session_data VALUES ('Hello');
>
> In this way, the table needn't exist until the first invocation of
> getValue() upon the first access of the view, since the code will be
> recompiled during the first access, correct?
>
> Mike Mascari
>
>
>
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match