Bind values for query with plpgsql in Yii2

Question

I execute SQL query with Yii2 DAO.

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

But it fails with error:

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

type column has INT type. I tried to set param type explicitly with [':t' => [0, \PDO::PARAM_INT]]. But the error is still here. If I concatenate the value right into the SQL string, it works, but that's not a solution. :t is only one parameter in this query.

Other simple SQL queries work successfully. This problem exists only for queries with procedures. If I run this query from the DataGrip, it works. But in PHP it fails.

Why does it not work and how can I bind params for such queries?

Answer 1

If you create an actual function and call it, you can pass values as parameters.

But while you execute a DO statement, the function body (enclosed in dollar-quotes $$ in your example) is just a string literal. No parameter passing and nothing returned. You would have to concatenate values as strings into the plpgsql code.

But you don't need either. Use a simple prepared statement instead. Much cheaper than looping anyway:

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

It's trivial to pass a parameter value now.

Aside: The result is arbitrary, unless you add ORDER BY to the OVER clause. That weakness is in your original, too.

Related:

• What are '$$' used for in PL/pgSQL