在PostgreSQL中使用PDO时如何忽略问号作为占位符

问题描述：

此问题可以被视为此

This question can be considered as duplicate of this Question. It does point to the same problem with PDO. But its workaround solution is a bit different as the target differ. I will post there the workaround for JSONB and the link to the PHP ticket.

当我准备以下查询时:

SELECT * FROM post WHERE locations ? :location;

发生以下警告:

警告:PDO :: prepare():SQLSTATE [HY093]:无效的参数编号:xx行上的/path/file.php中的混合命名和位置参数

Warning: PDO::prepare(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in /path/file.php on line xx

问号是有效的 PostgreSQL运算符，但PDO认为它是占位符.

The question mark is an valid PostgreSQL operator but PDO condsider it as a placeholder.

是否有适当的方法将PDO配置为忽略问号作为占位符?

Is there a proper way to configure PDO to ignore question mark as placeholders?

我将在下面发布解决方法.希望有更好的方法

修改我在PHP错误跟踪系统上添加了门票

Edit I add a ticket at PHP bug tracing system

答

这是解决我的问题的方法.它通过使用PostgreSQL函数替换?运算符来解决该问题.

This is a workaround to my problem. It solve the problem by using PostgreSQL function to replace the ? operator.

我不太喜欢它，因为它不能使PDO更符合PostgreSQL.但是我没有找到真正的解决方案.

I don't really like it because it does not make PDO more compliance to PostgreSQL. But I found no real solution.

CREATE FUNCTION json_key_exists(JSONB,TEXT) RETURNS BOOLEAN LANGUAGE SQL STABLE AS $f$
    SELECT $1 ? $2
$f$;

现在我可以使用查询了:

And now I can use the query:

SELECT * FROM post WHERE json_key_exists(locations, :location);

该变通办法是来自freenode #postgresql的神话般的RhodiumToad

The workaround was suggested by the fabulous RhodiumToad from freenode #postgresql

如@Abelisto所建议的那样，由于jsonb_exists(jsonb, text)是avialabe

As @Abelisto suggested, there is no need to create the function above as jsonb_exists(jsonb, text) is avialabe

在PostgreSQL中使用PDO时如何忽略问号作为占位符

相关推荐