Re: Creating a session variable in Postgres

On Thu, 3 Jun 2004 09:04:43 +0200, "Nagib Abi Fadel"
<nagib(dot)abi-fadel(at)usj(dot)edu(dot)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');

From time to time you have to clean up the session table (delete old
entries, VACUUM ANALYSE).

If the value of your session variable X has no special meaning apart
from being unique, you don't need the session table, you just create a
sequence and use nextval/currval directly.

Or you might want to use pg_backend_pid(). It is documented here:
http://www.postgresql.org/docs/7.4/static/monitoring-stats.html.

Servus
Manfred