postgresql - 在 PL/pgSQL 函数中使用变量

Question

Postgres PL/pgSQL docs say :

For any SQL command that does not return rows, for example INSERT without a RETURNING clause, you can execute the command within a PL/pgSQL function just by writing the command.

Any PL/pgSQL variable name appearing in the command text is treated as a parameter, and then the current value of the variable is provided as the parameter value at run time.

但是当我在查询中使用变量名时，我得到一个错误:

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 会抛出错误。这应该怎么写？

Answer 1

您不能将参数名称放在单引号中 (''email'' 并且您不能“按原样”使用参数 email 因为它具有与表中的列同名。这种名称冲突是强烈建议不使用与其中一个表中的列同名的变量或参数的原因之一。你有处理此问题的三种选择:

1. 重命名变量。常见的命名约定是在参数前加上 p_ 前缀，例如p_email，然后在 insert

   中使用明确的名称

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

2. 第一个参数使用$1，第二个参数使用$2:

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

3. 在参数名前加上函数名:

   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());