From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | fasupport(at)allcoast(dot)net |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: feature requests |
Date: | 2007-07-08 15:54:02 |
Message-ID: | 7436F937-D036-4A05-A3C8-5AF9530298E6@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-07-08 16:11:18 | Re: [GENERAL] Unable to get postgres running after long time no vacuum |
Previous Message | Mike Gould | 2007-07-08 15:16:43 | feature requests |