使用PHP / PDO进行PostgreSQL UPDATE的RETURNING子句

Question

I execute a simple postgresql update as:

UPDATE data SET Gender=lower(tmp.champs2) 
FROM tmp WHERE data.email=tmp.champs1 
AND (data.Gender IS NULL OR data.Gender='') 
AND tmp.champs2 IS NOT NULL 
AND tmp.champs2!='' 
RETURNING Gender

The PostgreSQL documentation said we can get the number of rows affected with the RETURNING clause.

But how can I get this result with PHP, PDO?

I try something like:

echo $requete = "UPDATE data 
    SET ".$value."=lower(tmp.champs".$num.") 
    FROM tmp 
    WHERE data.email=tmp.champs".$email." 
    AND (data.".$value." IS NULL OR data.".$value."='') 
    AND tmp.champs".$num." IS NOT NULL 
    AND tmp.champs".$num."!='' 
    RETURNING ".$value;

    $db->exec($requete);

    $db->fetch(PDO::FETCH_NUM);

    echo $row[0];

The correct number of rows updated are not returned.

Answer 1

According to PDO's doc:

PDO::exec() executes an SQL statement in a single function call, returning the number of rows affected by the statement.

Your current code $db->exec($requete); ignores the return value, which is the mistake. This should be for instance:

$affected_rows = $db->exec($requete);

A RETURNING clause is not necessary to get the number of rows affected (besides, it doesn't exist in some backends that PDO supports). The purpose of RETURNING is to retrieve rows in addition to updating, all in the same query.

In the case where the RETURNING clause is necessary and results must be fetched, PDO::query should be used instead of PDO:exec(). The PHP code to fetch the results is the same as if the query was a SELECT.