diff options
Diffstat (limited to 'doc/src/sgml/html/typeconv-oper.html')
| -rw-r--r-- | doc/src/sgml/html/typeconv-oper.html | 247 |
1 files changed, 247 insertions, 0 deletions
diff --git a/doc/src/sgml/html/typeconv-oper.html b/doc/src/sgml/html/typeconv-oper.html new file mode 100644 index 0000000..fbcb13c --- /dev/null +++ b/doc/src/sgml/html/typeconv-oper.html @@ -0,0 +1,247 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>10.2. Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="typeconv-overview.html" title="10.1. Overview" /><link rel="next" href="typeconv-func.html" title="10.3. Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">10.2. Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="typeconv-overview.html" title="10.1. Overview">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="typeconv.html" title="Chapter 10. Type Conversion">Up</a></td><th width="60%" align="center">Chapter 10. Type Conversion</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="typeconv-func.html" title="10.3. Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="TYPECONV-OPER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">10.2. Operators</h2></div></div></div><a id="id-1.5.9.7.2" class="indexterm"></a><p> + 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 <a class="xref" href="sql-syntax-lexical.html#SQL-PRECEDENCE" title="4.1.6. Operator Precedence">Section 4.1.6</a> for more information. + </p><div class="procedure" id="id-1.5.9.7.4"><p class="title"><strong>Operator Type Resolution</strong></p><ol class="procedure" type="1"><li class="step" id="OP-RESOL-SELECT"><p> +Select the operators to be considered from the +<code class="classname">pg_operator</code> system catalog. If a non-schema-qualified +operator name was used (the usual case), the operators +considered are those with the matching name and argument count that are +visible in the current search path (see <a class="xref" href="ddl-schemas.html#DDL-SCHEMAS-PATH" title="5.9.3. The Schema Search Path">Section 5.9.3</a>). +If a qualified operator name was given, only operators in the specified +schema are considered. +</p><ol type="a" class="substeps"><li class="step"><p> +If the search path finds multiple operators with identical argument types, +only the one appearing earliest in the path is considered. Operators with +different argument types are considered on an equal footing regardless of +search path position. +</p></li></ol></li><li class="step" id="OP-RESOL-EXACT-MATCH"><p> +Check for an operator accepting exactly the input argument types. +If one exists (there can be only one exact match in the set of +operators considered), use it. Lack of an exact match creates a security +hazard when calling, via qualified name + <a href="#ftn.OP-QUALIFIED-SECURITY" class="footnote"><sup class="footnote" id="OP-QUALIFIED-SECURITY">[9]</sup></a> +(not typical), any operator found in a schema that permits untrusted users to +create objects. In such situations, cast arguments to force an exact match. +</p><ol type="a" class="substeps"><li class="step" id="OP-RESOL-EXACT-UNKNOWN"><p> +If one argument of a binary operator invocation is of the <code class="type">unknown</code> type, +then assume it is the same type as the other argument for this check. +Invocations involving two <code class="type">unknown</code> inputs, or a prefix operator +with an <code class="type">unknown</code> input, will never find a match at this step. +</p></li><li class="step" id="OP-RESOL-EXACT-DOMAIN"><p> +If one argument of a binary operator invocation is of the <code class="type">unknown</code> +type and the other is of a domain type, next check to see if there is an +operator accepting exactly the domain's base type on both sides; if so, use it. +</p></li></ol></li><li class="step" id="OP-RESOL-BEST-MATCH"><p> +Look for the best match. +</p><ol type="a" class="substeps"><li class="step"><p> +Discard candidate operators for which the input types do not match +and cannot be converted (using an implicit conversion) to match. +<code class="type">unknown</code> literals are +assumed to be convertible to anything for this purpose. If only one +candidate remains, use it; else continue to the next step. +</p></li><li class="step"><p> +If any input argument is of a domain type, treat it as being of the +domain's base type for all subsequent steps. This ensures that domains +act like their base types for purposes of ambiguous-operator resolution. +</p></li><li class="step"><p> +Run through all candidates and keep those with the most exact matches +on input types. Keep all candidates if none have exact matches. +If only one candidate remains, use it; else continue to the next step. +</p></li><li class="step"><p> +Run through all candidates and keep those that accept preferred types (of the +input data type's type category) at the most positions where type conversion +will be required. +Keep all candidates if none accept preferred types. +If only one candidate remains, use it; else continue to the next step. +</p></li><li class="step"><p> +If any input arguments are <code class="type">unknown</code>, check the type +categories accepted at those argument positions by the remaining +candidates. At each position, select the <code class="type">string</code> category +if any +candidate accepts that category. (This bias towards string is appropriate +since an unknown-type literal looks like a string.) Otherwise, if +all the remaining candidates accept the same type category, select that +category; otherwise fail because the correct choice cannot be deduced +without more clues. Now discard +candidates that do not accept the selected type category. Furthermore, +if any candidate accepts a preferred type in that category, +discard candidates that accept non-preferred types for that argument. +Keep all candidates if none survive these tests. +If only one candidate remains, use it; else continue to the next step. +</p></li><li class="step" id="OP-RESOL-LAST-UNKNOWN"><p> +If there are both <code class="type">unknown</code> and known-type arguments, and all +the known-type arguments have the same type, assume that the +<code class="type">unknown</code> arguments are also of that type, and check which +candidates can accept that type at the <code class="type">unknown</code>-argument +positions. If exactly one candidate passes this test, use it. +Otherwise, fail. +</p></li></ol></li></ol></div><p> +Some examples follow. +</p><div class="example" id="id-1.5.9.7.6"><p class="title"><strong>Example 10.1. Square Root Operator Type Resolution</strong></p><div class="example-contents"><p> +There is only one square root operator (prefix <code class="literal">|/</code>) +defined in the standard catalog, and it takes an argument of type +<code class="type">double precision</code>. +The scanner assigns an initial type of <code class="type">integer</code> to the argument +in this query expression: +</p><pre class="screen"> +SELECT |/ 40 AS "square root of 40"; + square root of 40 +------------------- + 6.324555320336759 +(1 row) +</pre><p> + +So the parser does a type conversion on the operand and the query +is equivalent to: + +</p><pre class="screen"> +SELECT |/ CAST(40 AS double precision) AS "square root of 40"; +</pre><p> +</p></div></div><br class="example-break" /><div class="example" id="id-1.5.9.7.7"><p class="title"><strong>Example 10.2. String Concatenation Operator Type Resolution</strong></p><div class="example-contents"><p> +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. +</p><p> +An example with one unspecified argument: +</p><pre class="screen"> +SELECT text 'abc' || 'def' AS "text and unknown"; + + text and unknown +------------------ + abcdef +(1 row) +</pre><p> +</p><p> +In this case the parser looks to see if there is an operator taking <code class="type">text</code> +for both arguments. Since there is, it assumes that the second argument should +be interpreted as type <code class="type">text</code>. +</p><p> +Here is a concatenation of two values of unspecified types: +</p><pre class="screen"> +SELECT 'abc' || 'def' AS "unspecified"; + + unspecified +------------- + abcdef +(1 row) +</pre><p> +</p><p> +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, <code class="type">text</code>, is used as the specific +type to resolve the unknown-type literals as. +</p></div></div><br class="example-break" /><div class="example" id="id-1.5.9.7.8"><p class="title"><strong>Example 10.3. Absolute-Value and Negation Operator Type Resolution</strong></p><div class="example-contents"><p> +The <span class="productname">PostgreSQL</span> operator catalog has several +entries for the prefix operator <code class="literal">@</code>, all of which implement +absolute-value operations for various numeric data types. One of these +entries is for type <code class="type">float8</code>, which is the preferred type in +the numeric category. Therefore, <span class="productname">PostgreSQL</span> +will use that entry when faced with an <code class="type">unknown</code> input: +</p><pre class="screen"> +SELECT @ '-4.5' AS "abs"; + abs +----- + 4.5 +(1 row) +</pre><p> +Here the system has implicitly resolved the unknown-type literal as type +<code class="type">float8</code> before applying the chosen operator. We can verify that +<code class="type">float8</code> and not some other type was used: +</p><pre class="screen"> +SELECT @ '-4.5e500' AS "abs"; + +ERROR: "-4.5e500" is out of range for type double precision +</pre><p> +</p><p> +On the other hand, the prefix operator <code class="literal">~</code> (bitwise negation) +is defined only for integer data types, not for <code class="type">float8</code>. So, if we +try a similar case with <code class="literal">~</code>, we get: +</p><pre class="screen"> +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. +</pre><p> +This happens because the system cannot decide which of the several +possible <code class="literal">~</code> operators should be preferred. We can help +it out with an explicit cast: +</p><pre class="screen"> +SELECT ~ CAST('20' AS int8) AS "negation"; + + negation +---------- + -21 +(1 row) +</pre><p> +</p></div></div><br class="example-break" /><div class="example" id="id-1.5.9.7.9"><p class="title"><strong>Example 10.4. Array Inclusion Operator Type Resolution</strong></p><div class="example-contents"><p> +Here is another example of resolving an operator with one known and one +unknown input: +</p><pre class="screen"> +SELECT array[1,2] <@ '{1,2,3}' as "is subset"; + + is subset +----------- + t +(1 row) +</pre><p> +The <span class="productname">PostgreSQL</span> operator catalog has several +entries for the infix operator <code class="literal"><@</code>, but the only two that +could possibly accept an integer array on the left-hand side are +array inclusion (<code class="type">anyarray</code> <code class="literal"><@</code> <code class="type">anyarray</code>) +and range inclusion (<code class="type">anyelement</code> <code class="literal"><@</code> <code class="type">anyrange</code>). +Since none of these polymorphic pseudo-types (see <a class="xref" href="datatype-pseudo.html" title="8.21. Pseudo-Types">Section 8.21</a>) are considered preferred, the parser cannot +resolve the ambiguity on that basis. +However, <a class="xref" href="typeconv-oper.html#OP-RESOL-LAST-UNKNOWN" title="Step 3.f">Step 3.f</a> 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.) +</p></div></div><br class="example-break" /><div class="example" id="id-1.5.9.7.10"><p class="title"><strong>Example 10.5. Custom Operator on a Domain Type</strong></p><div class="example-contents"><p> +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 +</p><pre class="screen"> +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'; +</pre><p> +This query will not use the custom operator. The parser will first see if +there is a <code class="type">mytext</code> <code class="literal">=</code> <code class="type">mytext</code> operator +(<a class="xref" href="typeconv-oper.html#OP-RESOL-EXACT-UNKNOWN" title="Step 2.a">Step 2.a</a>), which there is not; +then it will consider the domain's base type <code class="type">text</code>, and see if +there is a <code class="type">text</code> <code class="literal">=</code> <code class="type">text</code> operator +(<a class="xref" href="typeconv-oper.html#OP-RESOL-EXACT-DOMAIN" title="Step 2.b">Step 2.b</a>), which there is; +so it resolves the <code class="type">unknown</code>-type literal as <code class="type">text</code> and +uses the <code class="type">text</code> <code class="literal">=</code> <code class="type">text</code> operator. +The only way to get the custom operator to be used is to explicitly cast +the literal: +</p><pre class="screen"> +SELECT * FROM mytable WHERE val = text 'foo'; +</pre><p> +so that the <code class="type">mytext</code> <code class="literal">=</code> <code class="type">text</code> 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. +</p></div></div><br class="example-break" /><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.OP-QUALIFIED-SECURITY" class="footnote"><p><a href="#OP-QUALIFIED-SECURITY" class="para"><sup class="para">[9] </sup></a> + 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 <a class="link" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.9.6. Usage Patterns">secure schema usage + pattern</a>. + </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="typeconv-overview.html" title="10.1. Overview">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="typeconv.html" title="Chapter 10. Type Conversion">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="typeconv-func.html" title="10.3. Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">10.1. Overview </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 10.3. Functions</td></tr></table></div></body></html>
\ No newline at end of file |