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]http://www.varlena.com/GeneralBits/.

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

$$ 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

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