[Please start a new message rather than replying to an old one to
start a new topic.]
On Jul 8, 2007, at 10:16 , Mike Gould wrote:
I would like to see a CREATE Variable dataname datatype added. The
scope of these variables would be global.
CREATE TABLE global_int_variables
(
global_int_variable TEXT PRIMARY KEY
, global_int_value INTEGER NOT NULL
);
-- set a new variable
INSERT INTO global_int_variables (global_int_variable,
global_int_value) VALUES ('foo', 42);
-- get current value
SELECT global_int_value FROM global_int_variables WHERE
global_int_variable = 'foo';
-- assign a new value to an existing variable
UPDATE global_int_variables SET global_int_value = 3 WHERE
global_int_variable = 'foo';
-- undefine a variable
DELETE FROM global_int_variables WHERE global_int_variable = 'foo';
You can also wrap these in functions for convenience (untested):
CREATE FUNCTION set_global_int(p_var TEXT, p_val INTEGER) RETURNS VOID
LANGUAGE plpgsql AS $_$
BEGIN
UPDATE global_int_variables
SET global_int_value = p_val
WHERE global_int_variable = p_var;
IF NOT FOUND THEN
INSERT INTO global_int_variables (global_int_variable,
global_int_value)
VALUES (p_var, p_val);
END IF;
RETURN;
END
$_$;
CREATE FUNCTION get_global_int(TEXT) RETURNS INTEGER
LANGUAGE SQL as $_$
SELECT global_int_value FROM global_int_variables WHERE
global_int_variable = $1;
$_$;
CREATE FUNCTION undef_global_int(TEXT) RETURNS VOID
LANGUAGE SQL as $_$
DELETE FROM global_int_variables WHERE global_int_variable = $1;
$_$;
test=# SELECT set_global_int('foo', 5);
set_global_int
----------------
(1 row)
test=# SELECT get_global_int('foo');
get_global_int
----------------
5
(1 row)
test=# SELECT get_global_int('bar');
get_global_int
----------------
(1 row)
test=# SELECT set_global_int('bar', 9);
set_global_int
----------------
(1 row)
test=# SELECT get_global_int('bar');
get_global_int
----------------
9
(1 row)
test=# SELECT set_global_int('bar', 10);
set_global_int
----------------
(1 row)
test=# SELECT get_global_int('bar');
get_global_int
----------------
10
(1 row)
test=# SELECT undef_global_int('bar');
undef_global_int
------------------
(1 row)
test=# SELECT get_global_int('bar');
get_global_int
----------------
(1 row)
Along this same line I would like to see a way to have a trigger or
rule fired upon connection initialization. This would allow for
these type of variables to be SET along with other defaults that
need to be setup on a per connection basis from the server side
instead of the client application.
If you want the variables to be used on a per-connection basis,
you'll want to use TEMP tables. As for setup per connection, you may
be able to do something with a psqlrc file if using psql. Otherwise I
believe you'll need to handle this setup yourself (which I believe
could be someone fully automated using stored procedures). I know
this has come up on the lists before but I don't recall the specifics
of the discussion (e.g., if it's been decided that this isn't
something that will be added to PostgreSQL or whether no one's made a
specific proposal). You may want to search the archives for more
information.
Michael Glaesemann
grzm seespotcode net
