Postgresql 中文操作指南

38.14. User-Defined Operators #

每个运算符都是对调用执行实际工作的底层函数的“语法糖”；因此，在创建运算符之前，必须先创建底层函数。然而，一个运算符是 not merely 语法糖，因为它携带附加信息，有助于查询计划优化使用该运算符的查询。下一节将专门解释这些附加信息。

Every operator is “syntactic sugar” for a call to an underlying function that does the real work; so you must first create the underlying function before you can create the operator. However, an operator is not merely syntactic sugar, because it carries additional information that helps the query planner optimize queries that use the operator. The next section will be devoted to explaining that additional information.

PostgreSQL 支持前缀和中缀运算符。运算符可以重载；即，可以将同一运算符名称用于具有不同的运算对象数量和类型的运算符。在执行查询时，系统会根据所提供运算对象的数目和类型来确定要调用的运算符。

PostgreSQL supports prefix and infix operators. Operators can be overloaded; that is, the same operator name can be used for different operators that have different numbers and types of operands. When a query is executed, the system determines the operator to call from the number and types of the provided operands.

这是一个为添加两个复数创建运算符的示例。我们假设我们已经创建了类型 _complex_的定义（请参阅 Section 38.13）。首先，我们需要一个执行该工作的函数，然后我们可以定义运算符：

Here is an example of creating an operator for adding two complex numbers. We assume we’ve already created the definition of type complex (see Section 38.13). First we need a function that does the work, then we can define the operator:

CREATE FUNCTION complex_add(complex, complex)
    RETURNS complex
    AS 'filename', 'complex_add'
    LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR + (
    leftarg = complex,
    rightarg = complex,
    function = complex_add,
    commutator = +
);

现在，我们可以执行类似这样的查询：

Now we could execute a query like this:

SELECT (a + b) AS c FROM test_complex;

        c
-----------------
 (5.2,6.05)
 (133.42,144.95)

我们在此演示了如何创建二元运算符。要创建前缀运算符，只需省略 leftarg。function 子句和参数子句是 CREATE OPERATOR 中唯一必需的项。示例中所示的 commutator 子句是对查询优化器的一个可选提示。关于 commutator 和其他优化器提示的更多详细信息将在下一节中出现。

We’ve shown how to create a binary operator here. To create a prefix operator, just omit the leftarg. The function clause and the argument clauses are the only required items in CREATE OPERATOR. The commutator clause shown in the example is an optional hint to the query optimizer. Further details about commutator and other optimizer hints appear in the next section.