Postgresql 中文操作指南

45.4. Global Values in PL/Perl #

您可以使用全局哈希 %_SHARED 在当前会话的生命周期内将数据存储在函数调用之间，包括代码引用。

You can use the global hash %_SHARED to store data, including code references, between function calls for the lifetime of the current session.

这是一个共享数据的简单示例：

Here is a simple example for shared data:

CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
    if ($_SHARED{$_[0]} = $_[1]) {
        return 'ok';
    } else {
        return "cannot set shared variable $_[0] to $_[1]";
    }
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
    return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

SELECT set_var('sample', 'Hello, PL/Perl!  How''s tricks?');
SELECT get_var('sample');

这是一个使用代码引用的稍微复杂一些的示例：

Here is a slightly more complicated example using a code reference:

CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
    $_SHARED{myquote} = sub {
        my $arg = shift;
        $arg =~ s/(['\\])/\\$1/g;
        return "'$arg'";
    };
$$ LANGUAGE plperl;

SELECT myfuncs(); /* initializes the function */

/* Set up a function that uses the quote function */

CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
    my $text_to_quote = shift;
    my $qfunc = $_SHARED{myquote};
    return &$qfunc($text_to_quote);
$$ LANGUAGE plperl;

（可以使用单行 return $_SHARED{myquote}→($[0] 替换上述内容）；以牺牲可读性为代价。）

(You could have replaced the above with the one-liner return $_SHARED{myquote}→($[0]);_ at the expense of readability.)

出于安全原因，PL/Perl 以该角色的单独的 Perl 解释器执行由任何一个 SQL 角色调用的函数。这防止了一个用户意外或恶意地干扰另一个用户的 PL/Perl 函数的行为。每个这样的解释器都有自己的 %_SHARED 变量值和其他全局状态。因此，只有当由相同的 SQL 角色执行时，两个 PL/Perl 函数才会共享相同的 %_SHARED 值。在单个会话在多个 SQL 角色下执行代码（通过 SECURITY DEFINER 函数、使用 SET ROLE 等）的应用程序中，您可能需要采取明确的步骤来确保 PL/Perl 函数可以通过 %_SHARED 共享数据。要做到这一点，请确保应通信的函数归同一位用户所有，并将其标记为 SECURITY DEFINER。您当然必须小心，确保这样的函数不会被用来执行任何意外的事情。

For security reasons, PL/Perl executes functions called by any one SQL role in a separate Perl interpreter for that role. This prevents accidental or malicious interference by one user with the behavior of another user’s PL/Perl functions. Each such interpreter has its own value of the %_SHARED variable and other global state. Thus, two PL/Perl functions will share the same value of %_SHARED if and only if they are executed by the same SQL role. In an application wherein a single session executes code under multiple SQL roles (via SECURITY DEFINER functions, use of SET ROLE, etc.) you may need to take explicit steps to ensure that PL/Perl functions can share data via %_SHARED. To do that, make sure that functions that should communicate are owned by the same user, and mark them SECURITY DEFINER. You must of course take care that such functions can’t be used to do anything unintended.