Re: Creating a session variable in Postgres
От | Nagib Abi Fadel |
---|---|
Тема | Re: Creating a session variable in Postgres |
Дата | |
Msg-id | 004e01c449fc$c4e2a330$f664a8c0@nagib обсуждение исходный текст |
Ответ на | Re: Creating a session variable in Postgres (Bruce Momjian <pgman@candle.pha.pa.us>) |
Ответы |
Re: Creating a session variable in Postgres
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: