Postgresql 中文操作指南

10.2. Operators #

由运算符表达式引用的特定运算符是使用以下过程确定的。请注意，此过程间接受到所涉及运算符优先级的影响，因为这将确定哪些子表达式被视为哪些运算符的输入。有关更多信息，请参阅 Section 4.1.6。

The specific operator that is referenced by an operator expression is determined using the following procedure. Note that this procedure is indirectly affected by the precedence of the operators involved, since that will determine which sub-expressions are taken to be the inputs of which operators. See Section 4.1.6 for more information.

Operator Type Resolution

后面有一些示例。

Some examples follow.

Example 10.1. Square Root Operator Type Resolution

标准目录中仅定义了一个平方根运算符（前缀 |/），它接收类型 double precision 的参数。扫描器会将初始类型 integer 指定给此查询表达式中的参数：

There is only one square root operator (prefix |/) defined in the standard catalog, and it takes an argument of type double precision. The scanner assigns an initial type of integer to the argument in this query expression:

SELECT |/ 40 AS "square root of 40";
 square root of 40
-------------------
 6.324555320336759
(1 row)

因此，解析器对操作数执行类型转换，查询等价于：

So the parser does a type conversion on the operand and the query is equivalent to:

SELECT |/ CAST(40 AS double precision) AS "square root of 40";

Example 10.2. String Concatenation Operator Type Resolution

字符串类似语法用于处理字符串类型和复杂扩展类型。未指定类型的字符串与可能的候选运算符匹配。

A string-like syntax is used for working with string types and for working with complex extension types. Strings with unspecified type are matched with likely operator candidates.

一个未指定参数的示例：

An example with one unspecified argument:

SELECT text 'abc' || 'def' AS "text and unknown";

 text and unknown
------------------
 abcdef
(1 row)

在此情况下，解析器会查看有没有运算符将 text 作为两个参数。由于存在，它假定应将第二个参数解释为类型 text。

In this case the parser looks to see if there is an operator taking text for both arguments. Since there is, it assumes that the second argument should be interpreted as type text.

以下是两个未指定类型的值的连接：

Here is a concatenation of two values of unspecified types:

SELECT 'abc' || 'def' AS "unspecified";

 unspecified
-------------
 abcdef
(1 row)

在此情况下，没有任何类型的初始提示可用，因为查询中未指定任何类型。因此，解析器会查找所有候选运算符，并找出有接受字符串类别和位字符串类别输入的候选运算符。由于有字符串类别可用时会优先选择字符串类别，因此会选择该类别，然后使用字符串的首选类型 text 作为解析未知类型文字的具体类型。

In this case there is no initial hint for which type to use, since no types are specified in the query. So, the parser looks for all candidate operators and finds that there are candidates accepting both string-category and bit-string-category inputs. Since string category is preferred when available, that category is selected, and then the preferred type for strings, text, is used as the specific type to resolve the unknown-type literals as.

Example 10.3. Absolute-Value and Negation Operator Type Resolution

PostgreSQL 运算符目录中有一个前缀运算符 @ 的多个条目，所有这些条目都为各种数字数据类型实现绝对值运算。其中一个条目是类型 float8 的，该类型是数字类别中的首选类型。因此，当处理 unknown 输入时，PostgreSQL 会使用该条目：

The PostgreSQL operator catalog has several entries for the prefix operator @, all of which implement absolute-value operations for various numeric data types. One of these entries is for type float8, which is the preferred type in the numeric category. Therefore, PostgreSQL will use that entry when faced with an unknown input:

SELECT @ '-4.5' AS "abs";
 abs
-----
 4.5
(1 row)

在此，系统在应用所选运算符之前已将未知类型文字隐式解析为类型 float8。我们可以验证使用了 float8 而不是其他类型：

Here the system has implicitly resolved the unknown-type literal as type float8 before applying the chosen operator. We can verify that float8 and not some other type was used:

SELECT @ '-4.5e500' AS "abs";

ERROR:  "-4.5e500" is out of range for type double precision

另一方面，前缀运算符 ~（按位取反）仅定义为整数数据类型，不适用于 float8。因此，如果我们尝试使用 ~ 进行类似的操作，我们得到：

On the other hand, the prefix operator ~ (bitwise negation) is defined only for integer data types, not for float8. So, if we try a similar case with ~, we get:

SELECT ~ '20' AS "negation";

ERROR:  operator is not unique: ~ "unknown"
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.

之所以会发生这种情况，是因为系统无法确定应优先选用哪几个可能的 ~ 运算符。我们可以通过显式强制转换来帮助该系统：

This happens because the system cannot decide which of the several possible ~ operators should be preferred. We can help it out with an explicit cast:

SELECT ~ CAST('20' AS int8) AS "negation";

 negation
----------
      -21
(1 row)

Example 10.4. Array Inclusion Operator Type Resolution

以下是解析具有一个已知输入和一个未知输入的运算符的另一个示例：

Here is another example of resolving an operator with one known and one unknown input:

SELECT array[1,2] <@ '{1,2,3}' as "is subset";

 is subset
-----------
 t
(1 row)

PostgreSQL 操作符目录中有几个 infix 操作符 <@ 的条目，但只有两个可能在左侧接受一个整数数组，即数组包含 (anyarray <@ anyarray) 和范围包含 (anyelement <@ anyrange)。由于这些多态伪类型（参见 Section 8.21）都不被认为是首选的，解析器无法在此基础上消除歧义。然而， Step 3.f 告诉它假设未知类型文本是与其他输入相同的类型，即整数数组。现在只有两个操作符中的一个可以匹配，因此选择了数组包含。（如果选择了范围包含，我们会得到一个错误，因为字符串没有成为范围文本的正确格式。）

The PostgreSQL operator catalog has several entries for the infix operator <@, but the only two that could possibly accept an integer array on the left-hand side are array inclusion (anyarray <@ anyarray) and range inclusion (anyelement <@ anyrange). Since none of these polymorphic pseudo-types (see Section 8.21) are considered preferred, the parser cannot resolve the ambiguity on that basis. However, Step 3.f tells it to assume that the unknown-type literal is of the same type as the other input, that is, integer array. Now only one of the two operators can match, so array inclusion is selected. (Had range inclusion been selected, we would have gotten an error, because the string does not have the right format to be a range literal.)

Example 10.5. Custom Operator on a Domain Type

用户有时尝试仅仅对某个域类型声明运算符。这是可能的，但并不会像看起来那么有用，因为运算符解析规则旨在选择适用于该域的基本类型的运算符。作为示例，考虑

Users sometimes try to declare operators applying just to a domain type. This is possible but is not nearly as useful as it might seem, because the operator resolution rules are designed to select operators applying to the domain’s base type. As an example consider

CREATE DOMAIN mytext AS text CHECK(...);
CREATE FUNCTION mytext_eq_text (mytext, text) RETURNS boolean AS ...;
CREATE OPERATOR = (procedure=mytext_eq_text, leftarg=mytext, rightarg=text);
CREATE TABLE mytable (val mytext);

SELECT * FROM mytable WHERE val = 'foo';

此查询将不会使用自定义操作符。解析器会首先查看是否有 mytext = mytext 操作符 ( Step 2.a)，但没有；然后它将考虑域的基类型 text，并查看是否有 text = text 操作符 ( Step 2.b)，是有；因此它将 unknown- 类型的文本解析为 text 并使用 text = text 操作符。让自定义操作符被使用的唯一方式是对文本进行显式转换：

This query will not use the custom operator. The parser will first see if there is a mytext = mytext operator (Step 2.a), which there is not; then it will consider the domain’s base type text, and see if there is a text = text operator (Step 2.b), which there is; so it resolves the unknown-type literal as text and uses the text = text operator. The only way to get the custom operator to be used is to explicitly cast the literal:

SELECT * FROM mytable WHERE val = text 'foo';

以便根据精确匹配规则立即找到 mytext = text 运算符。如果达到最佳匹配规则，则会主动区分域类型运算符。如果不然，则此类运算符将创建过多的模棱两可的运算符失败问题，因为强制类型转换规则会始终考虑将域强制类型转换成其基类型或在其基类型强制类型转换，因此在所有类似于基类型运算符命名的情况下，域运算符都可用。

so that the mytext = text operator is found immediately according to the exact-match rule. If the best-match rules are reached, they actively discriminate against operators on domain types. If they did not, such an operator would create too many ambiguous-operator failures, because the casting rules always consider a domain as castable to or from its base type, and so the domain operator would be considered usable in all the same cases as a similarly-named operator on the base type.

[9 ] 如果使用非架构限定的名称，则不会出现风险，因为包含允许不受信任的用户创建对象的架构的搜索路径并非 secure schema usage pattern 。

[9] The hazard does not arise with a non-schema-qualified name, because a search path containing schemas that permit untrusted users to create objects is not a secure schema usage pattern.