Re: can I define own variables?

[brian <brian@xxxxxxxxxxxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match