Re: can I define own variables?

Andreas wrote:
> Hi,
>
> can I define connection-global variables within a ODBC connection ?
>
> Like that:
> connect to the DB
> set my_user_id = 42
> Later a trigger would set a timestamp and the user-id when a record gets
> updated.
>
> obviously different connections would need differnt variable-values.
>
> the variable should only live until the connection gets terminated.
>
> this user-id represent users of my application and I can't use postgres'
> internal user-ids because I have the data sitting in a operational
> server and another one for developing and testing, so the postgres ids
> arent necessarily consistent between the 2 server-systems.
> My application has it's own user management and those keys are used for
> rel. integrity.
>

I can't remember where i got this. It was probably this list or the
General Bits newsletter [1].

CREATE OR REPLACE FUNCTION set_id(name text, val INT) RETURNS text AS $$
     if ($_SHARED{$_[0]} = $_[1])
     {
         return 'ok';
     }
     else
     {
         return "can't set shared variable $_[0] to $_[1]";
     }

$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_id(name text) RETURNS INT IMMUTABLE AS $$
     return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

SELECT set_id('my_user_id', 42);

SELECT CAST(get_id('my_user_id') AS INT);

[1] http://www.varlena.com/GeneralBits/

brian