oracle Postgresql 存储过程中基于会话的全局变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/414541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Session based global variable in Postgresql stored procedure?
提问by dacracot
In Oracle's PL/SQL I can create a session based global variable with the package definition. With Postgresql's PLpg/SQL, it doesn't seem possible since there are no packages, only independent procedures and functions.
在 Oracle 的 PL/SQL 中，我可以使用包定义创建一个基于会话的全局变量。使用 Postgresql 的 PLpg/SQL，似乎不可能，因为没有包，只有独立的过程和函数。
Here is the syntax for PL/SQL to declare g_spool_key as a global...
这是 PL/SQL 将 g_spool_key 声明为全局的语法...
CREATE OR REPLACE PACKAGE tox IS
g_spool_key spool.key%TYPE := NULL;
TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE;
PROCEDURE begin_spool;
PROCEDURE into_spool
(
in_txt IN spool.txt%TYPE
);
PROCEDURE reset_spool;
FUNCTION end_spool
RETURN t_spool;
FUNCTION timestamp
RETURN VARCHAR2;
END tox;
How would I implement a session based global variable with PLpg/SQL?
我将如何使用 PLpg/SQL 实现基于会话的全局变量？
回答by Patryk Kordylewski
You could define some custom-variable-classes in your postgresql.conf and use it as connection-variables in your stored-procedure. See the docs.
您可以在 postgresql.conf 中定义一些自定义变量类，并将其用作存储过程中的连接变量。请参阅文档。
Usage example for a custom-variable-class "imos":
自定义变量类“imos”的用法示例：
imos=> set imos.testvar to 'foobar';
SET
Time: 0.379 ms
imos=> show imos.testvar;
imos.testvar
--------------
foobar
(1 row)
Time: 0.333 ms
imos=> set imos.testvar to 'bazbar';
SET
Time: 0.144 ms
imos=> show imos.testvar;
imos.testvar
--------------
bazbar
(1 row)
In stored-procedures you can use the built-in function current_setting('imos.testvar').
在存储过程中，您可以使用内置函数current_setting('imos.testvar')。
回答by Ben
Another option would be to create a temporary table, and use it to store all of your temporary variables
另一种选择是创建一个临时表，并使用它来存储所有临时变量
CREATE TEMPORARY TABLE tmp_vars(
name varchar(64),
value varchar(64),
PRIMARY KEY (name)
);
You could even create a stored procedure to manage everything, creating the table if it doesn't yet exist. One for retrieval and one for storage.
您甚至可以创建一个存储过程来管理所有内容，如果表尚不存在，则创建该表。一种用于检索，一种用于存储。
回答by Ben
PostgreSQL doesn't support global (session) variables, but you should use some tricks
PostgreSQL 不支持全局（会话）变量，但你应该使用一些技巧
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_II#Any_other_session_variableshttp://www.postgresql.org/docs/8.3/static/plperl-global.html
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_II#Any_other_session_variables http://www.postgresql.org/docs/8.3/static/plperl-global.html
regards Pavel Stehule
问候 Pavel Stehule
回答by porkbird
回答by dacracot
From the Postgresql forums...
从Postgresql 论坛...
So, a couple of questions....
- Can you declare global values from plpgsql?
- If so, is there a way of avoiding namespace pollution? (perhaps the equivalent to Oracle's use of plsql package variables)
所以，有几个问题......
- 你能从 plpgsql 中声明全局值吗？
- 如果是这样，有没有办法避免命名空间污染？（或许相当于Oracle对plsql包变量的使用）
plpgsql does not have global variables.
plpgsql 没有全局变量。
回答by Elazar Leibovich
An example PL/pgsqlscript that stores and retrieves global variables from a table:
PL/pgsql从表中存储和检索全局变量的示例脚本：
CREATE TABLE global_vars (name TEXT PRIMARY KEY, value TEXT);
CREATE FUNCTION put_var(key TEXT, data TEXT) RETURNS VOID AS '
BEGIN
LOOP
UPDATE global_vars SET value = data WHERE name = key;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO global_vars(name,value) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
' LANGUAGE plpgsql;
CREATE FUNCTION get_var(key TEXT) RETURNS TEXT AS '
DECLARE
result TEXT;
BEGIN
SELECT value FROM global_vars where name = key INTO result;
RETURN result;
END;
' LANGUAGE plpgsql;
CREATE FUNCTION del_var(key TEXT) RETURNS VOID AS '
BEGIN
DELETE FROM global_vars WHERE name = key;
END;
' LANGUAGE plpgsql;
