Postgresql存储过程中基于会话的全局变量？

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实现基于会话的全局变量?

6 个解决方案

#1

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')。

#2

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.

您甚至可以创建一个存储过程来管理所有内容,如果该表尚不存在则创建该表。一个用于检索,一个用于存储。

#3

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_variables http://www.postgresql.org/docs/8.3/static/plperl-global.html

regards Pavel Stehule

关于Pavel Stehule

#4

From the Postgresql forums...

来自Postgresql论坛......

So, a couple of questions....

所以,有几个问题....

Can you declare global values from plpgsql?

你能从plpgsql声明全局值吗?

If so, is there a way of avoiding namespace pollution? (perhaps the equivalent to Oracle's use of plsql package variables)

如果是这样,有没有办法避免命名空间污染? (也许相当于Oracle使用plsql包变量)

plpgsql does not have global variables.

plpgsql没有全局变量。

#5

Unfortunately there are no global variables in PL/pgSQL, although you can find ones in other PL languages that come with PostgreSQL, specifically in PL/Perl, PL/Python and PL/Tcl

不幸的是PL / pgSQL中没有全局变量,尽管你可以在PostgreSQL附带的其他PL语言中找到它们,特别是PL / Perl,PL / Python和PL / Tcl

#6

An example PL/pgsql script 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;

#1