当间隔是字符串连接的结果时，Postgresql 会报错

Question

使用 this answer将 (date + interval) 转换为日期。只有在我的例子中，日期和间隔是其他字符串的切片和骰子结果的字符串。问题是，当我为 date 而不是 interval 构造字符串时它起作用。

因此，以下语句有效:

SELECT (date '2013-01-01');
SELECT (interval '53 days');
SELECT (date '2013-01-01' + interval '53 days');

现在我想通过 substring 合成一些在 date 和 interval 之后传递的字符串-ing 一些其他字符串(想想存储过程，其中我们对传递的参数进行操作):

这适用于 date:

SELECT date (substring('2015015' from 1 for 4)||'-01-01')::text;

但是 interval 失败了:

SELECT interval (substring('2015015' from 5)||' days')::text;

错误信息:

ERROR:  syntax error at or near "substring"

如果我使用 CAST(x as INTERVAL) 或等效地使用 x::interval 显式转换为 interval，它实际上有效:

SELECT CAST((substring('2015015' from 5)||' days')::text AS INTERVAL);

或等同于:

SELECT ((substring('2015015' from 5)||' days')::text)::interval;

为什么 date TEXT 无论 TEXT 是如何放置的都有效，但与 interval 相同只适用于直接文本，但不是合成的。

我使用的是 Postgres 9.4。

Answer 1

那是因为 date 实际上是 PostgreSQL 中的一个函数。还有一个 interval 函数，但是 you need to quote it :

SELECT "interval"(substring('2015015' from 5)||' days');

It is also possible to specify a type cast using a function-like syntax:

typename ( expression )

However, this only works for types whose names are also valid as function names. For example, double precision cannot be used this way, but the equivalent float8 can. Also, the names interval, time, and timestamp can only be used in this fashion if they are double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided.

The function-like syntax is in fact just a function call. When one of the two standard cast syntaxes is used to do a run-time conversion, it will internally invoke a registered function to perform the conversion. By convention, these conversion functions have the same name as their output type, and thus the "function-like syntax" is nothing more than a direct invocation of the underlying conversion function. Obviously, this is not something that a portable application should rely on.

(大胆强调是我的。)

date() 在这里可能是个异常(exception)，大多数数据库供应商都支持类似的东西。

但是，您似乎也可能构建错误的 date() 查询，因为

SELECT date (substring('2015015' from 1 for 4)||'-01-01')::text;

将首先将 '2015-01-01'(从 text)转换为 date，然后再转换为 text。此语法与 date literals 无关.那些只能这样表达:

SELECT date '<constant string without any expressions>';

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.