fryk napisaÅ(a):
Hi,
How to set such variable after (during?) client connection (PHP)?
I want to use it in view - so view could depends on it:
If I could set client's variable i.e. MY_VAR='hello' then I could do something like this:
CREATE VIEW my_view AS SELECT * FROM pg_tables WHERE tablename ~* (SELECT MY_VAR FROM ???)
You have (at least) two choices. First is to use temporary table, where you can store anything you wish and it will be visible and available for current connection/session.
Read more: http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html
Another solution is to use plperl - this is more flexible then the first one. plperl supports global values - go to:
http://www.postgresql.org/docs/8.0/interactive/plperl-global.html
and see examples set_var and get_var.
Or, in the attached file, I've used pltcl. Again, it uses global variables to store session data. If you know TCL you may be able to improve on these functions since I more or less cut and paste these together.
-- Richard Huxton Archonet Ltd
-- app_session(VARNAME, VALUE)
-- Defines a text variable and sets its value.
-- If you try to set the same VARNAME twice in one session, an error is returned.
-- If VALUE is null, just returns the value.
--
CREATE OR REPLACE FUNCTION app_session(text, text) RETURNS text AS '
upvar app_sess_vars a
if {![ argisnull 2 ]} {
if {[ info exists a($1) ]} {
elog ERROR "app_session(): Already set var $1 this session"
}
set a($1) $2
}
return $a($1)
' LANGUAGE pltcl;
-- app_session(VARNAME)
-- Returns the value of VARNAME (if set) or "UNDEFINED"
-- NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT source
-- in a table definition
--
CREATE OR REPLACE FUNCTION app_session(text) RETURNS text AS '
upvar app_sess_vars a
if {![ info exists a($1) ]} {
return "UNDEFINED"
}
return $a($1)
' LANGUAGE pltcl IMMUTABLE;
-- app_session_int(VARNAME)
-- Returns the value of VARNAME (if set) or 0
-- NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT source
-- in a table definition
--
CREATE OR REPLACE FUNCTION app_session_int(text) RETURNS int4 AS '
upvar app_sess_vars a
if {![ info exists a($1) ]} {
return 0
}
return $a($1)
' LANGUAGE pltcl IMMUTABLE;
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
