I would like to see a CREATE Variable dataname datatype added. The scopeof these variables would be global. 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.

Best Regards,

Michael Gould
All Coast Intermodal Services, Inc.
904-376-7030
_____

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
To: Naz Gassiep [mailto:naz@mira.net]
Cc: pgsql-general@postgresql.org
Sent: Sat, 07 Jul 2007 19:50:03 -0400
Subject: Re: [GENERAL] Changing DB Encodings

Naz Gassiep <naz@mira.net> writes:
> Tom Lane wrote:
>> No, that implies a lack of error checking.

> Surely, then, that's a bug? Shouldn't postmaster check if a DB is inan
> encoding that differs from the selected locale?

Yeah, it should. Whether it can is a different question.

Part of the problem here is the lack of a reliable way to tell
*which* encoding is implied by a locale. On some systems you
can get a poorly-standardized string name for the locale's encoding;
on others you can't get anything.

There's been some experimental code in initdb for awhile now that tries
to guess encoding from locale. I have not heard reports of it failing
lately, so maybe we could promote it into a hard error check, or at
least a backend-side warning at CREATE DATABASE time. It still won't
help on old systems without nl_langinfo(CODESET), though. (But how
many of those are left? That call is specified by the Single Unix
Spec. Anybody know if it works on Windows?)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match