在PL/pgSQL函数中使用变量

Question

在PL/pgSQL函数中使用变量

postgresqlparameter-passingnaming-conventionsplpgsqlquotes

3

对于任何不返回行的SQL命令，例如没有RETURNING子句的INSERT命令，您可以通过编写该命令在PL/pgSQL函数内部执行该命令。

出现在命令文本中的任何PL/pgSQL变量名称都被视为参数，然后在运行时提供变量的当前值作为参数值。

但是当我在我的查询中使用变量名时，会出现错误：

ERROR:  syntax error at or near "email"
LINE 16: ...d,email,password) values(identity_id,current_ts,''email'',''...

这是我的函数：

CREATE OR REPLACE FUNCTION app.create_identity(email varchar,passwd varchar)
RETURNS integer as $$
DECLARE
    current_ts          integer;
    new_identity_id     integer;
    int_max             integer;
    int_min             integer;
BEGIN
    SELECT extract(epoch FROM now())::integer INTO current_ts;
    int_min:=-2147483648;
    int_max:= 2147483647;
    LOOP
        BEGIN
            SELECT floor(int_min + (int_max - int_min + 1) * random()) INTO new_identity_id;
            IF new_identity_id != 0 THEN
                INSERT into app.identity(identity_id,date_inserted,email,password) values(identity_id,current_ts,''email'',''passwd'');
                RETURN new_identity_id;
            END IF;
        EXCEPTION
            WHEN unique_violation THEN
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

为什么我在查询中使用变量时，Postgres会抛出错误？应该如何编写呢？

- Nulik

2个回答

2

@a_horse的回答解决了您实际提出的问题，并澄清了引用问题和命名冲突。

关于引用：

在PostgreSQL中插入带有单引号的文本

关于命名冲突（plpgsql的行为略有改变）：

更好的解决方案

我建议采用完全不同的方法来开始：

CREATE OR REPLACE FUNCTION app.create_identity(_email text, _passwd text
                                             , OUT new_identity_id int) AS
$func$
DECLARE
   _current_ts int := extract(epoch FROM now());
BEGIN
   LOOP
      --+ Generate compeltely random int4 numbers +-----------------------------
      -- integer (= int4) in Postgres is a signed integer occupying 4 bytes   --
      -- int4 ranges from -2147483648 to +2147483647, i.e. -2^31 to 2^31 - 1  --
      -- Multiply bigint 4294967296 (= 2^32) with random() (0.0 <= x < 1.0)   --
      --   trunc() the resulting (positive!) float8 - cheaper than floor()    -- 
      --   add result to -2147483648 and cast the next result back to int4    --
      -- The result fits the int4 range *exactly*                             --
      --------------------------------------------------------------------------
      INSERT INTO app.identity
            (identity_id, date_inserted,  email ,  password)
      SELECT _random_int, _current_ts  , _email , _passwd
      FROM  (SELECT (bigint '-2147483648'       -- could be int, but sum is bigint anyway
                   + bigint '4294967296' * random())::int) AS t(_random_int)  -- random int
      WHERE  _random_int <> 0                   -- exclude 0 (no insert)
      ON     CONFLICT (identity_id) DO NOTHING  -- no exception raised!
      RETURNING identity_id                     -- return *actually* inserted identity_id
      INTO   new_identity_id;                   -- OUT parameter, returned at end

      EXIT WHEN FOUND;                          -- exit after success
      -- maybe add counter and raise exception when exceeding n (100?) iterations
   END LOOP;
END
$func$  LANGUAGE plpgsql;

主要观点

你的随机整数计算会导致错误的integer out of range，因为中间项int_max - int_min + 1使用了integer，但结果不适合。我建议使用更便宜、更正确的算法。
加入异常子句的块比没有异常子句的块显然更昂贵。幸运的是，您实际上不需要一开始就引发异常。使用一个UPSERT（INSERT ... ON CONFLICT ... DO NOTHING），以便廉价而优雅地解决这个问题 (Postgres 9.5+)。
手册：

提示：带有EXCEPTION子句的块的进入和退出成本要高得多，因此，在无需使用EXCEPTION的情况下，请勿使用EXCEPTION。
您也不需要额外的IF结构。使用SELECT与WHERE。
使new_identity_id成为一个OUT参数以简化操作。
使用RETURNING子句，并将结果中的identity_id直接插入OUT参数。除了更简单的代码和更快的执行，还有一个额外而微妙的好处：您获得的是实际插入的值。如果表上有触发器或规则，则可能与您使用的INSERT不同。
在 PL/pgSQL 中，赋值是相对昂贵的。为了编写高效的代码，应尽量减少这些赋值。
您也可以删除最后剩下的变量_current_ts，并在子查询中进行计算，然后根本不需要一个DECLARE。我将其留下，因为如果函数循环多次计算它可能会有意义...
剩下的仅仅是一个SQL命令，包装成一个LOOP来重试直到成功。
如果有可能您的表可能会溢出（使用所有或大多数int4数字） - 严格来说，总是存在机会 - 我建议添加一个计数器，在100次迭代后引发异常，以避免无限循环。

- Erwin Brandstetter

哇！这就是我所说的工程师精神！我猜你正在使用单个查询（全部在一个查询中），因为在解析后它们执行非常快。现在必须升级我的9.4安装。谢谢！ - Nulik

@Nulik：基本上是的。在幕后，plpgsql 中的每个赋值都是一个单独的 SELECT（非常基本和快速，但仍然如此）。只要不变得复杂，全部在一个查询中完成通常更快。 - Erwin Brandstetter

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

您不能将参数名称用单引号括起来（''email''），也不能直接使用参数email，因为它与表中的列名相同。这种名称冲突是高度建议不要在变量或参数与表中某个列名相同时使用的原因之一。您有三种选择处理这个问题：

rename the variable. A common naming convention is to prefix parameters with p_ e.g. p_email, then use the un-ambigous names in the insert
```
INSERT into app.identity(identity_id,date_inserted,email,password) 
values(identity_id,current_ts,p_email,p_password);
```

use the $1 for the first parameter and $2 for the second:

INSERT into app.identity(identity_id,date_inserted,email,password) 
values(identity_id,current_ts,$1,$2);

prefix the parameter name with the function name:

INSERT into app.identity(identity_id,date_inserted,email,password) 
values(identity_id,current_ts,create_identity.email,create_identity.password);

我强烈建议选择选项1。

不相关的是：如果您不从表中检索这些值，则无需使用SELECT语句来分配变量值。

SELECT extract(epoch FROM now())::integer INTO current_ts;

可以简化为:

current_ts := extract(epoch FROM now())::integer;

并且。

SELECT floor(int_min + (int_max - int_min + 1) * random()) INTO new_identity_id;

为了

new_identity_id := floor(int_min + (int_max - int_min + 1) * random());