Re: Client's variables - Mailing list pgsql-general

Marek Lewczuk wrote:
> 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;