Operators

Operator Type Resolution

1. Select the operators to be considered from the pg_operator system catalog. If an operator with the same parameter type and number of parameters can be found, the operator is the final operator. If multiple alternative operators are found, the most appropriate one is selected.
2. Search for the optimal match.

   1. Discard candidate operators for which the input types do not match and cannot be implicitly converted to match. The unknown text can be converted to any type in this case. If only one candidate operator is left, use it. Otherwise, go to the next step.

   2. Review all candidate operators and retain the operator that best matches the input type. Here, domains are treated as their base types. If no exact match exists, keep all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.

   3. Examine all candidate operators and keep those that accept the most preferred type positions (within the input data type's type category) when type conversion is necessary. If no operator accepts the preferred type, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.

   4. If any input parameter is of the unknown type, inspect the type category of the corresponding parameter position among the remaining candidate operators. At each position capable of accepting the string type category, use the string type (as favoring strings is reasonable since unknown text resembles a string). Additionally, if all remaining candidate operators accept the same type category, choose that category. Otherwise, report an error (since making the correct choice requires more information). Now eliminate candidate operators that do not accept the chosen type category. Moreover, if any candidate accepts a preferred type within that category, exclude candidates that accept non-preferred types for that argument. If no operator can be kept, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.

   5. If both unknown and known parameters exist, and all known parameters share the same type, assume the unknown parameter also falls into that type, then verify which candidate operators accept this type at the unknown parameter position. If exactly one operator fits, use it. Otherwise, raise an error.

Examples

Example 1: Factorial operator type resolution. Only one factorial operator (postfix !) exists in the system catalog, taking bigint as its parameter. The scanner initially assigns the bigint type to the parameter in the following query expression:

1
2
3
4
5
6

postgres=#SELECT 40 ! AS "40 factorial";

                   40 factorial
--------------------------------------------------
 815915283247897734345611269596115894272000000000
(1 row)

The analyzer performs type conversion on the parameter, rendering the query equivalent to:

1

postgres=#SELECT CAST(40 AS bigint) ! AS "40 factorial";

Example 2: String concatenation operator type analysis. A string-like syntax applies to both strings and complex extended types. Unspecified-type strings are matched against all potential candidate operators. An instance involving an untyped parameter follows:

1
2
3
4
5

postgres=#SELECT text 'abc' || 'def' AS "text and unknown";
 text and unknown
------------------
 abcdef
(1 row)

In this example, the analyzer seeks an operator where both parameters are of type text. Such an operator indeed exists, with both parameters being text.

Below demonstrates concatenating two values of undefined types:

1
2
3
4
5

postgres=#SELECT 'abc' || 'def' AS "unspecified";
 unspecified
-------------
 abcdef
(1 row)

As no type is specified in the query, there is no initial clue regarding the type here. Consequently, the parser scans through all candidate operators, discovering ones that accommodate either the string or bit-string type categories. Given the preference for the string type category, the preferred type text within this category is selected as the declared type for interpreting the unknown type text.

Example 3: Absolute value and negation operator type analysis. Several entries in the DataArts Fabric SQL operator table relate to the prefix operator @, designed to execute absolute value computations across diverse numerical types. Among them, one caters specifically to the float8 type, deemed the preferred type within the numerical type category. Thus, upon encountering unknown inputs, DataArts Fabric SQL defaults to using this type:

1
2
3
4
5

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

Herein, prior to applying the chosen operator, the system implicitly transforms the unknown-type text into the float8 type.

Example 4: Array containment operator type analysis. Consider a situation where the operator flanks a known type alongside an unknown type:

1
2
3
4
5

postgres=#SELECT array[1,2] <@ '{1,2,3}' as "is subset";
 is subset
-----------
 t
(1 row)

Within DataArts Fabric SQL's pg_operator table, numerous entries correspond to the infix operator <@, yet only array containment (anyarray <@ anyarray) and range containment (anyelement <@ anyrange) permit an integer array on the left side. Since polymorphic pseudo-types are not regarded as preferred operator types, the resolver fails to disambiguate based thereon. Nonetheless, the ultimate guideline for identifying the optimal match stipulates presuming the unknown-type literal aligns with another known input type—that is, exclusively one of the pair of operators may align—hence opting for array containment. (Selecting range containment would trigger an error due to improper string formatting.)