Add range_minus_multi and multirange_minus_multi functions

The existing range_minus function raises an exception when the range is
"split", because then the result can't be represented by a single range.
For example '[0,10)'::int4range - '[4,5)' would be '[0,4)' and '[5,10)'.

This commit adds new set-returning functions so that callers can get
results even in the case of splits. There is no risk of an exception for
multiranges, but a set-returning function lets us handle them the same
way we handle ranges.

Both functions return zero results if the subtraction would give an
empty range/multirange.

The main use-case for these functions is to implement UPDATE/DELETE FOR
PORTION OF, which must compute the application-time of "temporal
leftovers": the part of history in an updated/deleted row that was not
changed. To preserve the untouched history, we will implicitly insert
one record for each result returned by range/multirange_minus_multi.
Using a set-returning function will also let us support user-defined
types for application-time update/delete in the future.

Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com

1 files changed, 42 insertions, 0 deletions

diff --git a/doc/src/sgml/func/func-range.sgml b/doc/src/sgml/func/func-range.sgml
index 2dc40348a57..3c5a34796a1 100644
--- a/doc/src/sgml/func/func-range.sgml
+++ b/doc/src/sgml/func/func-range.sgml
@@ -842,6 +842,29 @@
         <returnvalue>[1,4)</returnvalue>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>range_minus_multi</primary>
+        </indexterm>
+        <function>range_minus_multi</function> ( <type>anyrange</type>, <type>anyrange</type> )
+        <returnvalue>setof anyrange</returnvalue>
+       </para>
+       <para>
+        Returns the non-empty range(s) remaining after subtracting the second range from the first.
+        One row is returned for each range, so if the second range splits the first into two parts,
+        there will be two results. If the subtraction yields an empty range, no rows are returned.
+       </para>
+       <para>
+        <literal>range_minus_multi('[0,10)'::int4range, '[3,4)'::int4range)</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+ [0,3)
+ [4,10)
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
@@ -1041,6 +1064,25 @@
 </programlisting>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>multirange_minus_multi</primary>
+        </indexterm>
+        <function>multirange_minus_multi</function> ( <type>anymultirange</type>, <type>anymultirange</type> )
+        <returnvalue>setof anymultirange</returnvalue>
+       </para>
+       <para>
+        Returns the non-empty multirange(s) remaining after subtracting the second multirange from the first.
+        If the subtraction yields an empty multirange, no rows are returned.
+        Two rows are never returned, because a single multirange can always accommodate any result.
+       </para>
+       <para>
+        <literal>multirange_minus_multi('{[0,10)}'::int4multirange, '{[3,4)}'::int4multirange)</literal>
+        <returnvalue>{[0,3), [4,10)}</returnvalue>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>