在 Yii2 中使用 plpgsql 绑定查询值

Question

我用Yii2 DAO执行SQL查询。

$db->createCommand("
  DO $$
  DECLARE
    rec RECORD;
    pos INT := 0;
  BEGIN
    FOR rec IN (SELECT * FROM table1 WHERE "type" = :t LOOP
      UPDATE table1    SET position = pos    WHERE id = rec.id;
      pos := pos + 2;
    END LOOP;
  END;
  $$ language 'plpgsql'
", [':t' => 0])->execute();

但它失败并显示错误：

SQLSTATE[42P18]: Indeterminate datatype: 7
ERROR:  could not determine data type of parameter $1

type列具有INT类型。我尝试使用 [':t' => [0, PDO::PARAM_INT]] 显式设置参数类型。但错误仍然存在。如果我将值直接连接到 SQL 字符串中，它可以工作，但这不是解决方案。 :t只是此查询中的一个参数。

其他简单的 SQL 查询可以成功工作。此问题仅存在于具有过程的查询中。如果我从 DataGrip 运行此查询，它可以工作。但是在PHP中它失败了。

为什么它不起作用，我如何为此类查询绑定参数？

Answer 1

如果创建实际函数并调用它，则可以将值作为参数传递。

但是，当您执行 DO 语句时，函数体(包含在美元引号中$$在您的示例中(只是一个字符串文字。没有参数传递，也没有返回任何内容。您必须将值作为字符串连接到 plpgsql 代码中。

但你也不需要。请改用简单的预准备语句。无论如何，比循环便宜得多：

UPDATE table1 t
SET    position = t1.pos
FROM  (
   SELECT id, (row_number() OVER () - 1) * 2 AS pos 
   FROM   table1
   WHERE  "type" = :t
   ) t1
WHERE  t.id = t1.id

现在传递参数值是微不足道的。

旁白：结果是任意的，除非您在OVER子句中添加ORDER BY。这个弱点在你的原著中也有。

相关：

• PL/pgSQL中的"$$"是什么用
的