"date $1"参数化查询中的 PostgreSQL 语法错误

Question

尝试参数化我的 SQL 查询(使用 libpq 函数 PQexecParams )，我陷入语法错误:

SELECT date $1

错误是:

ERROR:  syntax error at or near "$1"

Answer 1

Prepared statements

可以找到对此的解释 in the chapter Constants of Other Types of the manual :

The ::, CAST(), and function-call syntaxes can also be used to specify run-time type conversions of arbitrary expressions, as discussed in Section 4.2.9. To avoid syntactic ambiguity, the type 'string' syntax can only be used to specify the type of a simple literal constant. Another restriction on the type 'string' syntax is that it does not work for array types; use :: or CAST() to specify the type of an array constant.

大胆强调我的。

准备好的语句的参数实际上不是sting literals而是类型化的values，所以你不能使用type 'string'的形式。使用其他两种形式之一将值转换为不同的类型，就像您已经发现的那样。

例子:

PREPARE foo AS SELECT $1::date;

EXECUTE foo('2005-1-1');

类似于PQexecParams in the libpq C library

文档:

... In the SQL command text, attach an explicit cast to the parameter symbol to show what data type you will send. For example:

SELECT * FROM mytable WHERE x = $1::bigint;

This forces parameter $1 to be treated as bigint, whereas by default it would be assigned the same type as x. Forcing the parameter type decision, either this way or by specifying a numeric type OID, is strongly recommended. ...

备选方案，如上面引文中所述，是通过 paramTypes[] 传递相应数据类型的 OID - 如果您实际上需要类型转换。在大多数情况下，让 Postgres 从查询上下文中派生数据类型应该可以正常工作。

paramTypes[]

Specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server infers a data type for the parameter symbol in the same way it would do for an untyped literal string.

可以从系统目录中获取数据类型的OID pg_type :

SELECT oid FROM pg_type WHERE typname = 'date';

您必须使用正确的内部类型名称。例如:int4 代表 integer。
或者使用方便的方式转换为 regtype :

SELECT 'date'::regtype::oid;

这更加灵活，因为类型名称的已知别名也被接受。例如:int4、int 或 integer 表示 integer。