Re: can I define own variables?

Tom Lane wrote:
> Sam Mason <sam@samason.me.uk> writes:
>> On Sat, Oct 13, 2007 at 12:18:45AM +0200, Andreas wrote:
>>> can I define connection-global variables within a ODBC connection ?
>
>> You could use a temporary table, keyed on the variable name.  Not
>> particularly nice to use, but it works.  You can create accessor
>> functions reasonably easily if you want.
>
> Another possibility, if you only need to store and retrieve values
> and not do anything especially interesting with them, is to abuse
> the "custom GUC variable" facility.  This is meant to provide
> placeholder parameter settings for dynamically-loaded extension
> modules, but there's nothing stopping you from setting and reading
> a variable that in fact will never be used by any extension module.
>
> To do this, you need to set custom_variable_classes in postgresql.conf,
> perhaps
>     custom_variable_classes = user_vars
>
> and then you can do things like
>
>     SET user_vars.foo = whatever;
>     SHOW user_vars.bar;
>
> Of course, this is a hack of the first water, and you should expect
> that it might break sometime in the future.  But I don't think we'd
> break it without providing some alternative solution.

I've had several occasions when a user-defined variable would have come
in handy.  What is the scope of user_vars as you've defined them above?
  Are they unique to a connection?  A user_id?  Or defined globally?
Ideally, they would be connection-unique.  One place they would come in
really handy is with web-apps using connection pooling.  I could stuff
the userid of the person who connected to my secure website into a
user_var, then the database could access that to track user actions.
Right now, that is cumbersome to do, since the current_user is always
the same: the userid for the connection pool.

I'm actually surprised that such a common usage scenario has not forced
connection-unique user-defined variables into the spec.

--
Guy Rouillier