Re: feature requests - Mailing list pgsql-general

[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