我有一个在postgreSQL中编写的upsert查询
$statement = 'INSERT INTO "CharactersUnlockToBuyLevels"
("CharacterId", "LevelId", "AmountToBuy", "EagleStatueId", "Location",
"MapCoordinateTop", "MapCoordinateLeft")
VALUES
(:CharacterId, :LevelId, :AmountToBuy, :EagleStatueId, :Location,
:MapCoordinateTop, :MapCoordinateLeft)
ON CONFLICT
("CharacterId")
DO UPDATE
SET
"LevelId" = EXCLUDED."LevelId",
"AmountToBuy" = EXCLUDED."AmountToBuy",
"EagleStatueId" = EXCLUDED."EagleStatueId",
"Location" = EXCLUDED."Location",
"MapCoordinateTop" = EXCLUDED."MapCoordinateTop",
"MapCoordinateLeft" = EXCLUDED."MapCoordinateLeft"
RETURNING "CharacterUnlockToBuyLevelId"
';
这个查询正常工作,如果我在PgAdmin中运行它,我会得到期望的“CharacterUnlockToBuyLevelId”返回值。然而,如果我使用PHP的PDO准备并执行它,我似乎无法获得RETURNING的值。
$stmt = $this->db->prepare($statement);
$returnedId = $stmt->execute(array(
'CharacterId' => $characterId,
'LevelId' => $unlockMethod['LevelId'],
'AmountToBuy' => $unlockMethod['AmountToBuy'],
'EagleStatueId' => $unlockMethod['EagleStatueId'],
'Location' => $unlockMethod['Location'],
'MapCoordinateTop' => $unlockMethod['MapCoordinateTop'],
'MapCoordinateLeft' => $unlockMethod['MapCoordinateLeft'],
));
这只是返回了true,所以$returnedId
实际上不会保存id的值,而只是true。我该如何使用PDO的预处理语句从upsert查询中获取RETURNING值?
with i as (. . . ) select i.* from i
吗? - Gordon LinoffRETURNING
子句中的列将在结果集中返回。 - Gordon Linoff