Thread: Client's variables

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 ???)

Cheers!
___
fryk

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.

You decide what is the best solution for your needs.


In Polish:
Masz dwa wyjścia. Pierwszy wykorzystać tablice tymczasową, w której
możesz trzymać co chcesz. Będzie ona widoczna tylko dla jednego
użytkownika w obecnej sesji/połączeniu. Zobacz więcej:
http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html

Drugie rozwiązanie to skorzystać z plperl'a - to jest bardziej
elastyczne rozwiązanie. Plperl posiada możliwość tworzenia globalnych
zmiennych. Zobacz więcej:
http://www.postgresql.org/docs/8.0/interactive/plperl-global.html
Masz tam przykładowe funkcje, które możesz śmiało wykorzystać.

Sam zdecyduj co jest lepszym rozwiązaniem w Twojej aplikacji.


ML

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;