Re: Creating a session variable in Postgres

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');

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