Thread: can I define own variables?
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.
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
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. A real programming language server side would be great though! Sam
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.
regards, tom lane
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
Guy Rouillier <guyr-ml1@burntmail.com> writes:
> Tom Lane wrote:
>> 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.
> 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?
They'd act like any other GUC variable. The scope of a SET would be the
current connection, but there are other ways to set them with larger scope.
regards, tom lane
On 10/12/07, Guy Rouillier <guyr-ml1@burntmail.com> wrote: > 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. The Veil project might be worth looking at: http://veil.projects.postgresql.org/ It's designed with providing row-level security through views in mind, but it does that with global and session variable primitives.
> can I define connection-global variables within a ODBC connection ? Temp table, containing only 1 row, one column per variable. If you so wish, wrap it up in stored procedures for creating, setting, and accessing. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
http://www.postgresql.org/docs/8.3/static/libpq-envars.html use environment variables PGHOST is the host for Postgres PGHOSTADDR is the Numeric Host Address for Postgres PGPORT is the port for the PostgresServer to listen on PGDATABASE is the default Database Name PGUSER is the default Username to use to login PGPASSWORD is the password to use for default Username to login M-- ----- Original Message ----- From: "Scott Ribe" <scott_ribe@killerbytes.com> To: "Andreas" <maps.on@gmx.net>; <pgsql-general@postgresql.org> Sent: Saturday, October 13, 2007 11:05 AM Subject: Re: [GENERAL] can I define own variables? > > can I define connection-global variables within a ODBC connection ? > > Temp table, containing only 1 row, one column per variable. If you so wish, > wrap it up in stored procedures for creating, setting, and accessing. > > -- > Scott Ribe > scott_ribe@killerbytes.com > http://www.killerbytes.com/ > (303) 722-0567 voice > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Martin, Can you explain this a little further? My client-application would somehow set the the environment-variable PGUSER e.g. to 42. Then create an odbc connection with user='john' and password='doe' to the remote pg-server. Later the triggers that run on the server would access this client-local environment-variable for updates? How can the server-located function see client-local Env-Vars ? Regards Andreas Martin Gainty schrieb: > http://www.postgresql.org/docs/8.3/static/libpq-envars.html > use environment variables > > PGHOST is the host for Postgres > PGHOSTADDR is the Numeric Host Address for Postgres > PGPORT is the port for the PostgresServer to listen on > PGDATABASE is the default Database Name > PGUSER is the default Username to use to login > PGPASSWORD is the password to use for default Username to login > > M-- > > ----- Original Message ----- > From: "Scott Ribe" <scott_ribe@killerbytes.com> > To: "Andreas" <maps.on@gmx.net>; <pgsql-general@postgresql.org> > Sent: Saturday, October 13, 2007 11:05 AM > Subject: Re: [GENERAL] can I define own variables? > > > >>> can I define connection-global variables within a ODBC connection ? >>> >> Temp table, containing only 1 row, one column per variable. If you so >> > wish, > >> wrap it up in stored procedures for creating, setting, and accessing. >> >> -- >> Scott Ribe >> scott_ribe@killerbytes.com >> http://www.killerbytes.com/ >> (303) 722-0567 voice >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
Good Evening Andreas- Windows Control Panel/System/Advanced/EnvironmentVariables/Set System Variables as mentioned PGHOST=localhost all processes for all users will see these env vars Unix bash (to set system wide variables) go to /etc/profile vi .profile PGHOST=localhost export PGHOST :w! :q Hope this helps/ Martin-- ----- Original Message ----- From: "Andreas" <maps.on@gmx.net> To: "Martin Gainty" <mgainty@hotmail.com> Cc: <pgsql-general@postgresql.org> Sent: Sunday, October 14, 2007 1:30 PM Subject: Re: [GENERAL] can I define own variables? > Martin, > > Can you explain this a little further? > > My client-application would somehow set the the environment-variable > PGUSER e.g. to 42. > Then create an odbc connection with user='john' and password='doe' to > the remote pg-server. > Later the triggers that run on the server would access this client-local > environment-variable for updates? > > How can the server-located function see client-local Env-Vars ? > > Regards > Andreas > > > Martin Gainty schrieb: > > http://www.postgresql.org/docs/8.3/static/libpq-envars.html > > use environment variables > > > > PGHOST is the host for Postgres > > PGHOSTADDR is the Numeric Host Address for Postgres > > PGPORT is the port for the PostgresServer to listen on > > PGDATABASE is the default Database Name > > PGUSER is the default Username to use to login > > PGPASSWORD is the password to use for default Username to login > > > > M-- > > > > ----- Original Message ----- > > From: "Scott Ribe" <scott_ribe@killerbytes.com> > > To: "Andreas" <maps.on@gmx.net>; <pgsql-general@postgresql.org> > > Sent: Saturday, October 13, 2007 11:05 AM > > Subject: Re: [GENERAL] can I define own variables? > > > > > > > >>> can I define connection-global variables within a ODBC connection ? > >>> > >> Temp table, containing only 1 row, one column per variable. If you so > >> > > wish, > > > >> wrap it up in stored procedures for creating, setting, and accessing. > >> > >> -- > >> Scott Ribe > >> scott_ribe@killerbytes.com > >> http://www.killerbytes.com/ > >> (303) 722-0567 voice > >> > >> > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 1: if posting/reading through Usenet, please send an appropriate > >> subscribe-nomail command to majordomo@postgresql.org so that your > >> message can get through to the mailing list cleanly > >> > >> > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
Tom Lane wrote:
> Guy Rouillier <guyr-ml1@burntmail.com> writes:
>> Tom Lane wrote:
>>> 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.
>
>> 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?
>
> They'd act like any other GUC variable. The scope of a SET would be the
> current connection, but there are other ways to set them with larger scope.
8.2.0 on Windows doesn't like "user_vars" as a value for
custom_variable_classes, but "uservars" is okay.
I set that up, and using the SHOW command returns the set value. I
searched the archives and I couldn't identify a way to retrieve these
values in a trigger function. When I try to execute the following:
CREATE OR REPLACE FUNCTION assign_user_name() RETURNS trigger AS $$
BEGIN
NEW.user_name := (SHOW uservars.user_name);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
it complains:
ERROR: syntax error at or near "uservars"
LINE 1: SELECT (SHOW uservars.user_name)
^
QUERY: SELECT (SHOW uservars.user_name)
CONTEXT: SQL statement in PL/PgSQL function "assign_user_name" near line 2
********** Error **********
ERROR: syntax error at or near "uservars"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "assign_user_name" near line 2
I tried the same with a standard GUC (deadlock_timeout) and got the same
error, so this has nothing to do with custom classes. How can I
retrieve GUCs within a pl/pgsql function? Thanks.
--
Guy Rouillier
Guy Rouillier <guyr-ml1@burntmail.com> writes: > I set that up, and using the SHOW command returns the set value. I > searched the archives and I couldn't identify a way to retrieve these > values in a trigger function. http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE regards, tom lane
Tom Lane wrote: > Guy Rouillier <guyr-ml1@burntmail.com> writes: >> I set that up, and using the SHOW command returns the set value. I >> searched the archives and I couldn't identify a way to retrieve these >> values in a trigger function. > > http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE > Thank you, Tom. Since "select current_setting('uservars.user_name')" and "SHOW uservars.user_name" are both PostgreSQL extensions, why not allow them to be used interchangeably? In short, why isn't the following legal in a PL/pgsql procedure: NEW.user_name := (SHOW uservars.user_name); -- Guy Rouillier
> > http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE > > > > Thank you, Tom. Since "select current_setting('uservars.user_name')" > and "SHOW uservars.user_name" are both PostgreSQL extensions, why not > allow them to be used interchangeably? In short, why isn't the > following legal in a PL/pgsql procedure: > > NEW.user_name := (SHOW uservars.user_name); > It's little bit strange. It has not dificult implementation, but It's redundand. what is more readable and more simple? a) var := (SHOW system_var); b) var := (SELECT current_setting('system_var')); c) var := current_setting('system_var'); I thing so c). Pavel
Guy Rouillier <guyr-ml1@burntmail.com> writes:
> In short, why isn't the
> following legal in a PL/pgsql procedure:
> NEW.user_name := (SHOW uservars.user_name);
Because that isn't a legal SQL subexpression. (This isn't really the
approved way of using SELECT, either, since what you're writing is
a redundant sub-SELECT.) It seems that this works in recent plpgsql
versions, FWIW:
show autovacuum into r;
regards, tom lane