diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/btree.sgml | 181 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 123 | ||||
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 144 | ||||
| -rw-r--r-- | doc/src/sgml/xindex.sgml | 60 |
5 files changed, 432 insertions, 81 deletions
diff --git a/doc/src/sgml/btree.sgml b/doc/src/sgml/btree.sgml index 9f39edc742d..10abf90189e 100644 --- a/doc/src/sgml/btree.sgml +++ b/doc/src/sgml/btree.sgml @@ -207,7 +207,7 @@ <para> As shown in <xref linkend="xindex-btree-support-table"/>, btree defines - one required and one optional support function. + one required and two optional support functions. </para> <para> @@ -252,6 +252,185 @@ <filename>src/include/utils/sortsupport.h</filename>. </para> + <indexterm> + <primary>in_range support functions</primary> + </indexterm> + + <indexterm> + <primary>support functions</primary> + <secondary>in_range</secondary> + </indexterm> + + <para> + Optionally, a btree operator family may + provide <firstterm>in_range</firstterm> support function(s), registered + under support function number 3. These are not used during btree index + operations; rather, they extend the semantics of the operator family so + that it can support window clauses containing + the <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>PRECEDING</literal> + and <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>FOLLOWING</literal> frame bound types (see + <xref linkend="syntax-window-functions"/>). Fundamentally, the extra + information provided is how to add or subtract + an <replaceable>offset</replaceable> value in a way that is compatible + with the family's data ordering. + </para> + + <para> + An <function>in_range</function> function must have the signature +<synopsis> +in_range(<replaceable>val</replaceable> type1, <replaceable>base</replaceable> type1, <replaceable>offset</replaceable> type2, <replaceable>sub</replaceable> bool, <replaceable>less</replaceable> bool) +returns bool +</synopsis> + <replaceable>val</replaceable> and <replaceable>base</replaceable> must be + of the same type, which is one of the types supported by the operator + family (i.e., a type for which it provides an ordering). + However, <replaceable>offset</replaceable> could be of a different type, + which might be one otherwise unsupported by the family. An example is + that the built-in <literal>time_ops</literal> family provides + an <function>in_range</function> function that + has <replaceable>offset</replaceable> of type <type>interval</type>. + A family can provide <function>in_range</function> functions for any of + its supported types and one or more <replaceable>offset</replaceable> + types. Each <function>in_range</function> function should be entered + in <structname>pg_amproc</structname> + with <structfield>amproclefttype</structfield> equal to <type>type1</type> + and <structfield>amprocrighttype</structfield> equal to <type>type2</type>. + </para> + + <para> + The essential semantics of an <function>in_range</function> function + depend on the two boolean flag parameters. It should add or + subtract <replaceable>base</replaceable> + and <replaceable>offset</replaceable>, then + compare <replaceable>val</replaceable> to the result, as follows: + <itemizedlist> + <listitem> + <para> + if <literal>!</literal><replaceable>sub</replaceable> and + <literal>!</literal><replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal>>=</literal> + (<replaceable>base</replaceable> <literal>+</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + <listitem> + <para> + if <literal>!</literal><replaceable>sub</replaceable> + and <replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal><=</literal> + (<replaceable>base</replaceable> <literal>+</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + <listitem> + <para> + if <replaceable>sub</replaceable> + and <literal>!</literal><replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal>>=</literal> + (<replaceable>base</replaceable> <literal>-</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + <listitem> + <para> + if <replaceable>sub</replaceable> and <replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal><=</literal> + (<replaceable>base</replaceable> <literal>-</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + </itemizedlist> + Before doing so, the function should check the sign + of <replaceable>offset</replaceable>: if it is less than zero, raise + error <literal>ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE</literal> (22013) + with error text like <quote>invalid preceding or following size in window + function</quote>. (This is required by the SQL standard, although + nonstandard operator families might perhaps choose to ignore this + restriction, since there seems to be little semantic necessity for it.) + This requirement is delegated to the <function>in_range</function> + function so that the core code needn't understand what <quote>less than + zero</quote> means for a particular data type. + </para> + + <para> + An additional expectation is that <function>in_range</function> functions + should, if practical, avoid throwing an error + if <replaceable>base</replaceable> <literal>+</literal> + <replaceable>offset</replaceable> + or <replaceable>base</replaceable> <literal>-</literal> + <replaceable>offset</replaceable> would overflow. + The correct comparison result can be determined even if that value would + be out of the data type's range. Note that if the data type includes + concepts such as <quote>infinity</quote> or <quote>NaN</quote>, extra care + may be needed to ensure that <function>in_range</function>'s results agree + with the normal sort order of the operator family. + </para> + + <para> + The results of the <function>in_range</function> function must be + consistent with the sort ordering imposed by the operator family. + To be precise, given any fixed values of <replaceable>offset</replaceable> + and <replaceable>sub</replaceable>, then: + <itemizedlist> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is true for some <replaceable>val1</replaceable> + and <replaceable>base</replaceable>, it must be true for + every <replaceable>val2</replaceable> <literal><=</literal> + <replaceable>val1</replaceable> with the + same <replaceable>base</replaceable>. + </para> + </listitem> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is false for some <replaceable>val1</replaceable> + and <replaceable>base</replaceable>, it must be false for + every <replaceable>val2</replaceable> <literal>>=</literal> + <replaceable>val1</replaceable> with the + same <replaceable>base</replaceable>. + </para> + </listitem> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is true for some <replaceable>val</replaceable> + and <replaceable>base1</replaceable>, it must be true for + every <replaceable>base2</replaceable> <literal>>=</literal> + <replaceable>base1</replaceable> with the + same <replaceable>val</replaceable>. + </para> + </listitem> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is false for some <replaceable>val</replaceable> + and <replaceable>base1</replaceable>, it must be false for + every <replaceable>base2</replaceable> <literal><=</literal> + <replaceable>base1</replaceable> with the + same <replaceable>val</replaceable>. + </para> + </listitem> + </itemizedlist> + Analogous statements with inverted conditions hold + when <replaceable>less</replaceable> = false. + </para> + + <para> + If the type being ordered (<type>type1</type>) is collatable, + the appropriate collation OID will be passed to + the <function>in_range</function> function, using the standard + PG_GET_COLLATION() mechanism. + </para> + + <para> + <function>in_range</function> functions need not handle NULL inputs, and + typically will be marked strict. + </para> + </sect1> <sect1 id="btree-implementation"> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 487c7ff7507..640ff09a7b9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14729,8 +14729,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; partition through the last peer of the current row. This is likely to give unhelpful results for <function>last_value</function> and sometimes also <function>nth_value</function>. You can redefine the frame by - adding a suitable frame specification (<literal>RANGE</literal> or - <literal>ROWS</literal>) to the <literal>OVER</literal> clause. + adding a suitable frame specification (<literal>RANGE</literal>, + <literal>ROWS</literal> or <literal>GROUPS</literal>) to + the <literal>OVER</literal> clause. See <xref linkend="syntax-window-functions"/> for more information about frame specifications. </para> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 8a3e86b6db6..b5d3d3a071e 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -859,28 +859,39 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl The <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS } <replaceable>frame_start</replaceable> -{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] </synopsis> - where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be - one of + where <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> can be one of <synopsis> UNBOUNDED PRECEDING -<replaceable>value</replaceable> PRECEDING +<replaceable>offset</replaceable> PRECEDING CURRENT ROW -<replaceable>value</replaceable> FOLLOWING +<replaceable>offset</replaceable> FOLLOWING UNBOUNDED FOLLOWING </synopsis> + and <replaceable>frame_exclusion</replaceable> can be one of + +<synopsis> +EXCLUDE CURRENT ROW +EXCLUDE GROUP +EXCLUDE TIES +EXCLUDE NO OTHERS +</synopsis> + If <replaceable>frame_end</replaceable> is omitted it defaults to <literal>CURRENT ROW</literal>. Restrictions are that <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>, <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>, and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the - above list than the <replaceable>frame_start</replaceable> choice — for example - <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</replaceable> + above list of <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> options than + the <replaceable>frame_start</replaceable> choice does — for example + <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable> PRECEDING</literal> is not allowed. </para> @@ -888,33 +899,72 @@ UNBOUNDED FOLLOWING The default framing option is <literal>RANGE UNBOUNDED PRECEDING</literal>, which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</literal>; it sets the frame to be all rows from the partition start - up through the current row's last peer (a row that <literal>ORDER - BY</literal> considers equivalent to the current row, or all rows if there - is no <literal>ORDER BY</literal>). + up through the current row's last <firstterm>peer</firstterm> (a row + that the window's <literal>ORDER BY</literal> clause considers + equivalent to the current row), or all rows if there + is no <literal>ORDER BY</literal>. In general, <literal>UNBOUNDED PRECEDING</literal> means that the frame starts with the first row of the partition, and similarly <literal>UNBOUNDED FOLLOWING</literal> means that the frame ends with the last - row of the partition (regardless of <literal>RANGE</literal> or <literal>ROWS</literal> - mode). In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> - means that the frame starts or ends with the current row; but in - <literal>RANGE</literal> mode it means that the frame starts or ends with - the current row's first or last peer in the <literal>ORDER BY</literal> ordering. - The <replaceable>value</replaceable> <literal>PRECEDING</literal> and - <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases are currently only - allowed in <literal>ROWS</literal> mode. They indicate that the frame starts - or ends with the row that many rows before or after the current row. - <replaceable>value</replaceable> must be an integer expression not - containing any variables, aggregate functions, or window functions. - The value must not be null or negative; but it can be zero, which - selects the current row itself. - </para> - - <para> - Beware that the <literal>ROWS</literal> options can produce unpredictable + row of the partition, regardless + of <literal>RANGE</literal>, <literal>ROWS</literal> + or <literal>GROUPS</literal> mode. + In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> means + that the frame starts or ends with the current row; but + in <literal>RANGE</literal> or <literal>GROUPS</literal> mode it means + that the frame starts or ends with the current row's first or last peer + in the <literal>ORDER BY</literal> ordering. + The <replaceable>offset</replaceable> <literal>PRECEDING</literal> and + <replaceable>offset</replaceable> <literal>FOLLOWING</literal> options + vary in meaning depending on the frame mode. + In <literal>ROWS</literal> mode, the <replaceable>offset</replaceable> + is an integer indicating that the frame starts or ends that many rows + before or after the current row. + In <literal>GROUPS</literal> mode, the <replaceable>offset</replaceable> + is an integer indicating that the frame starts or ends that many peer + groups before or after the current row's peer group, where + a <firstterm>peer group</firstterm> is a group of rows that are + equivalent according to <literal>ORDER BY</literal>. + In <literal>RANGE</literal> mode, use of + an <replaceable>offset</replaceable> option requires that there be + exactly one <literal>ORDER BY</literal> column in the window definition. + Then the frame contains those rows whose ordering column value is no + more than <replaceable>offset</replaceable> less than + (for <literal>PRECEDING</literal>) or more than + (for <literal>FOLLOWING</literal>) the current row's ordering column + value. In these cases the data type of + the <replaceable>offset</replaceable> expression depends on the data + type of the ordering column. For numeric ordering columns it is + typically of the same type as the ordering column, but for datetime + ordering columns it is an <type>interval</type>. + In all these cases, the value of the <replaceable>offset</replaceable> + must be non-null and non-negative. Also, while + the <replaceable>offset</replaceable> does not have to be a simple + constant, it cannot contain variables, aggregate functions, or window + functions. + </para> + + <para> + The <replaceable>frame_exclusion</replaceable> option allows rows around + the current row to be excluded from the frame, even if they would be + included according to the frame start and frame end options. + <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the + frame. + <literal>EXCLUDE GROUP</literal> excludes the current row and its + ordering peers from the frame. + <literal>EXCLUDE TIES</literal> excludes any peers of the current + row from the frame, but not the current row itself. + <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the + default behavior of not excluding the current row or its peers. + </para> + + <para> + Beware that the <literal>ROWS</literal> mode can produce unpredictable results if the <literal>ORDER BY</literal> ordering does not order the rows - uniquely. The <literal>RANGE</literal> options are designed to ensure that - rows that are peers in the <literal>ORDER BY</literal> ordering are treated - alike; all peer rows will be in the same frame. + uniquely. The <literal>RANGE</literal> and <literal>GROUPS</literal> + modes are designed to ensure that rows that are peers in + the <literal>ORDER BY</literal> ordering are treated alike: all rows of + a given peer group will be in the frame or excluded from it. </para> <para> @@ -1982,17 +2032,6 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; </refsect2> <refsect2> - <title><literal>WINDOW</literal> Clause Restrictions</title> - - <para> - The SQL standard provides additional options for the window - <replaceable class="parameter">frame_clause</replaceable>. - <productname>PostgreSQL</productname> currently supports only the - options listed above. - </para> - </refsect2> - - <refsect2> <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title> <para> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index a938a21334b..f9905fb447b 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1802,21 +1802,28 @@ FROM generate_series(1,10) AS s(i); [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ <replaceable class="parameter">frame_clause</replaceable> ] </synopsis> - and the optional <replaceable class="parameter">frame_clause</replaceable> + The optional <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS } <replaceable>frame_start</replaceable> -{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] </synopsis> - where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be - one of + where <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> can be one of <synopsis> UNBOUNDED PRECEDING -<replaceable>value</replaceable> PRECEDING +<replaceable>offset</replaceable> PRECEDING CURRENT ROW -<replaceable>value</replaceable> FOLLOWING +<replaceable>offset</replaceable> FOLLOWING UNBOUNDED FOLLOWING </synopsis> + and <replaceable>frame_exclusion</replaceable> can be one of +<synopsis> +EXCLUDE CURRENT ROW +EXCLUDE GROUP +EXCLUDE TIES +EXCLUDE NO OTHERS +</synopsis> </para> <para> @@ -1856,11 +1863,14 @@ UNBOUNDED FOLLOWING The <replaceable class="parameter">frame_clause</replaceable> specifies the set of rows constituting the <firstterm>window frame</firstterm>, which is a subset of the current partition, for those window functions that act on - the frame instead of the whole partition. The frame can be specified in - either <literal>RANGE</literal> or <literal>ROWS</literal> mode; in either case, it - runs from the <replaceable>frame_start</replaceable> to the - <replaceable>frame_end</replaceable>. If <replaceable>frame_end</replaceable> is omitted, - it defaults to <literal>CURRENT ROW</literal>. + the frame instead of the whole partition. The set of rows in the frame + can vary depending on which row is the current row. The frame can be + specified in <literal>RANGE</literal>, <literal>ROWS</literal> + or <literal>GROUPS</literal> mode; in each case, it runs from + the <replaceable>frame_start</replaceable> to + the <replaceable>frame_end</replaceable>. + If <replaceable>frame_end</replaceable> is omitted, the end defaults + to <literal>CURRENT ROW</literal>. </para> <para> @@ -1871,24 +1881,91 @@ UNBOUNDED FOLLOWING </para> <para> - In <literal>RANGE</literal> mode, a <replaceable>frame_start</replaceable> of - <literal>CURRENT ROW</literal> means the frame starts with the current row's - first <firstterm>peer</firstterm> row (a row that <literal>ORDER BY</literal> considers - equivalent to the current row), while a <replaceable>frame_end</replaceable> of - <literal>CURRENT ROW</literal> means the frame ends with the last equivalent - <literal>ORDER BY</literal> peer. In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> simply means - the current row. + In <literal>RANGE</literal> or <literal>GROUPS</literal> mode, + a <replaceable>frame_start</replaceable> of + <literal>CURRENT ROW</literal> means the frame starts with the current + row's first <firstterm>peer</firstterm> row (a row that the + window's <literal>ORDER BY</literal> clause sorts as equivalent to the + current row), while a <replaceable>frame_end</replaceable> of + <literal>CURRENT ROW</literal> means the frame ends with the current + row's last peer row. + In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> simply + means the current row. </para> <para> - The <replaceable>value</replaceable> <literal>PRECEDING</literal> and - <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases are currently only - allowed in <literal>ROWS</literal> mode. They indicate that the frame starts - or ends the specified number of rows before or after the current row. - <replaceable>value</replaceable> must be an integer expression not + In the <replaceable>offset</replaceable> <literal>PRECEDING</literal> + and <replaceable>offset</replaceable> <literal>FOLLOWING</literal> frame + options, the <replaceable>offset</replaceable> must be an expression not containing any variables, aggregate functions, or window functions. - The value must not be null or negative; but it can be zero, which - just selects the current row. + The meaning of the <replaceable>offset</replaceable> depends on the + frame mode: + <itemizedlist> + <listitem> + <para> + In <literal>ROWS</literal> mode, + the <replaceable>offset</replaceable> must yield a non-null, + non-negative integer, and the option means that the frame starts or + ends the specified number of rows before or after the current row. + </para> + </listitem> + <listitem> + <para> + In <literal>GROUPS</literal> mode, + the <replaceable>offset</replaceable> again must yield a non-null, + non-negative integer, and the option means that the frame starts or + ends the specified number of <firstterm>peer groups</firstterm> + before or after the current row's peer group, where a peer group is a + set of rows that are equivalent in the <literal>ORDER BY</literal> + ordering. (If there is no <literal>ORDER BY</literal>, the whole + partition is one peer group.) + </para> + </listitem> + <listitem> + <para> + In <literal>RANGE</literal> mode, these options require that + the <literal>ORDER BY</literal> clause specify exactly one column. + The <replaceable>offset</replaceable> specifies the maximum + difference between the value of that column in the current row and + its value in preceding or following rows of the frame. The data type + of the <replaceable>offset</replaceable> expression varies depending + on the data type of the ordering column. For numeric ordering + columns it is typically of the same type as the ordering column, + but for datetime ordering columns it is an <type>interval</type>. + For example, if the ordering column is of type <type>date</type> + or <type>timestamp</type>, one could write <literal>RANGE BETWEEN + '1 day' PRECEDING AND '10 days' FOLLOWING</literal>. + The <replaceable>offset</replaceable> is still required to be + non-null and non-negative, though the meaning + of <quote>non-negative</quote> depends on its data type. + </para> + </listitem> + </itemizedlist> + In any case, the distance to the end of the frame is limited by the + distance to the end of the partition, so that for rows near the partition + ends the frame might contain fewer rows than elsewhere. + </para> + + <para> + Notice that in both <literal>ROWS</literal> and <literal>GROUPS</literal> + mode, <literal>0 PRECEDING</literal> and <literal>0 FOLLOWING</literal> + are equivalent to <literal>CURRENT ROW</literal>. This normally holds + in <literal>RANGE</literal> mode as well, for an appropriate + data-type-specific meaning of <quote>zero</quote>. + </para> + + <para> + The <replaceable>frame_exclusion</replaceable> option allows rows around + the current row to be excluded from the frame, even if they would be + included according to the frame start and frame end options. + <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the + frame. + <literal>EXCLUDE GROUP</literal> excludes the current row and its + ordering peers from the frame. + <literal>EXCLUDE TIES</literal> excludes any peers of the current + row from the frame, but not the current row itself. + <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the + default behavior of not excluding the current row or its peers. </para> <para> @@ -1896,9 +1973,9 @@ UNBOUNDED FOLLOWING which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</literal>. With <literal>ORDER BY</literal>, this sets the frame to be all rows from the partition start up through the current row's last - <literal>ORDER BY</literal> peer. Without <literal>ORDER BY</literal>, all rows of the partition are - included in the window frame, since all rows become peers of the current - row. + <literal>ORDER BY</literal> peer. Without <literal>ORDER BY</literal>, + this means all rows of the partition are included in the window frame, + since all rows become peers of the current row. </para> <para> @@ -1906,9 +1983,14 @@ UNBOUNDED FOLLOWING <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>, <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>, and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the - above list than the <replaceable>frame_start</replaceable> choice — for example - <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</replaceable> + above list of <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> options than + the <replaceable>frame_start</replaceable> choice does — for example + <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable> PRECEDING</literal> is not allowed. + But, for example, <literal>ROWS BETWEEN 7 PRECEDING AND 8 + PRECEDING</literal> is allowed, even though it would never select any + rows. </para> <para> diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index e40131473fe..9f5c0c3fb2c 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -401,7 +401,8 @@ </para> <para> - B-trees require a single support function, and allow a second one to be + B-trees require a comparison support function, + and allow two additional support functions to be supplied at the operator class author's option, as shown in <xref linkend="xindex-btree-support-table"/>. The requirements for these support functions are explained further in @@ -433,6 +434,13 @@ </entry> <entry>2</entry> </row> + <row> + <entry> + Compare a test value to a base value plus/minus an offset, and return + true or false according to the comparison result (optional) + </entry> + <entry>3</entry> + </row> </tbody> </tgroup> </table> @@ -971,7 +979,8 @@ DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint8cmp(int8, int8) , - FUNCTION 2 btint8sortsupport(internal) ; + FUNCTION 2 btint8sortsupport(internal) , + FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ; CREATE OPERATOR CLASS int4_ops DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS @@ -982,7 +991,8 @@ DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint4cmp(int4, int4) , - FUNCTION 2 btint4sortsupport(internal) ; + FUNCTION 2 btint4sortsupport(internal) , + FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ; CREATE OPERATOR CLASS int2_ops DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS @@ -993,7 +1003,8 @@ DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint2cmp(int2, int2) , - FUNCTION 2 btint2sortsupport(internal) ; + FUNCTION 2 btint2sortsupport(internal) , + FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ; ALTER OPERATOR FAMILY integer_ops USING btree ADD -- cross-type comparisons int8 vs int2 @@ -1042,7 +1053,13 @@ ALTER OPERATOR FAMILY integer_ops USING btree ADD OPERATOR 3 = (int2, int4) , OPERATOR 4 >= (int2, int4) , OPERATOR 5 > (int2, int4) , - FUNCTION 1 btint24cmp(int2, int4) ; + FUNCTION 1 btint24cmp(int2, int4) , + + -- cross-type in_range functions + FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) , + FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) , + FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) , + FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ; ]]> </programlisting> @@ -1181,6 +1198,39 @@ SELECT * FROM mytable ORDER BY somecol USING ~<~; </para> <para> + Another SQL feature that requires even more data-type-specific knowledge + is the <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>PRECEDING</literal>/<literal>FOLLOWING</literal> framing option + for window functions (see <xref linkend="syntax-window-functions"/>). + For a query such as +<programlisting> +SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) + FROM mytable; +</programlisting> + it is not sufficient to know how to order by <literal>x</literal>; + the database must also understand how to <quote>subtract 5</quote> or + <quote>add 10</quote> to the current row's value of <literal>x</literal> + to identify the bounds of the current window frame. Comparing the + resulting bounds to other rows' values of <literal>x</literal> is + possible using the comparison operators provided by the B-tree operator + class that defines the <literal>ORDER BY</literal> ordering — but + addition and subtraction operators are not part of the operator class, so + which ones should be used? Hard-wiring that choice would be undesirable, + because different sort orders (different B-tree operator classes) might + need different behavior. Therefore, a B-tree operator class can specify + an <firstterm>in_range</firstterm> support function that encapsulates the + addition and subtraction behaviors that make sense for its sort order. + It can even provide more than one in_range support function, in case + there is more than one data type that makes sense to use as the offset + in <literal>RANGE</literal> clauses. + If the B-tree operator class associated with the window's <literal>ORDER + BY</literal> clause does not have a matching in_range support function, + the <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>PRECEDING</literal>/<literal>FOLLOWING</literal> + option is not supported. + </para> + + <para> Another important point is that an equality operator that appears in a hash operator family is a candidate for hash joins, hash aggregation, and related optimizations. The hash operator family |