PDO::PARAM_INPUT_OUTPUT not working in PostgreSQL?

Question

I'm having trouble getting PDO::PARAM_INPUT_OUTPUT to work in PostgreSQL. PHP 8.1 (latest in Debian from Sury), PostgreSQL 13.6.

Procedure declaration:

CREATE OR REPLACE PROCEDURE public.procedure (
  a integer,
  inout b integer
)
AS
$body$
BEGIN
  b := a * b;
END;
$body$
LANGUAGE 'plpgsql'
SECURITY INVOKER;

Testing the procedure in SQL:

DO
$$
DECLARE b INT;
BEGIN
    b := 2;
    CALL public.procedure(3, b);
    RAISE NOTICE '%', b;
END
$$

It outputs:

NOTICE:  6

Testing in PHP:

<?php
declare(strict_types=1);

$connection_params = [];
$connection_params[\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION;

$pdo = new \PDO(
    'pgsql:user=user;password=password;dbname=somedb;host=127.0.0.1;port=5432',
    null,
    null,
    $connection_params
);

$sql = 'CALL public.procedure(?, ?)';
$stmt = $pdo->prepare($sql);

$a = 2;
$b = 3;

$stmt->bindParam(1, $a, \PDO::PARAM_INT, 10);
$stmt->bindParam(2, $b, \PDO::PARAM_INT | \PDO::PARAM_INPUT_OUTPUT, 10);

print "Values of bound parameters _before_ CALL:\n";
print "  1: {$a} 2: {$b}\n";

$stmt->execute();

print "Values of bound parameters _after_ CALL:\n";
print "  1: {$a} 2: {$b}\n";

But it outputs:

Values of bound parameters _before_ CALL:
  1: 2 2: 3
Values of bound parameters _after_ CALL:
  1: 2 2: 3

It should output:

Values of bound parameters _before_ CALL:
  1: 2 2: 3
Values of bound parameters _after_ CALL:
  1: 2 2: 6

What am I doing wrong?

Answer 1

See this GitHub bug report page: https://github.com/php/doc-en/issues/2309

Basically, the workaround for now is to add $stmt->fetch() after $stmt->execute() to populate an array with your (IN)OUT parameters, and use that to populate your variables (that you would normally expect to be auto-populated!).

This also means you can use BindValue() instead of BindParam().