Re: 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