Postgresql存储过程中基于会话的全局变量? [英] Session based global variable in Postgresql stored procedure?
问题描述
在Oracle的PL/SQL中,我可以使用包定义创建基于会话的全局变量.使用Postgresql的PLpg/SQL,似乎不可能,因为没有包,只有独立的过程和函数.
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.
这是PL/SQL将g_spool_key声明为全局变量的语法...
Here is the syntax for PL/SQL to declare g_spool_key as a global...
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;
如何使用PLpg/SQL实现基于会话的全局变量?
How would I implement a session based global variable with PLpg/SQL?
推荐答案
您可以在postgresql.conf中定义一些自定义变量类,并将其用作存储过程中的连接变量.参见文档.
You could define some custom-variable-classes in your postgresql.conf and use it as connection-variables in your stored-procedure. See the docs.
自定义变量类"imos"的用法示例:
Usage example for a custom-variable-class "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)
在存储过程中,您可以使用内置函数 current_setting( 'imos.testvar').
In stored-procedures you can use the built-in function current_setting('imos.testvar').
这篇关于Postgresql存储过程中基于会话的全局变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!