MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <af62a16b-9f88-4746-8e87-fdf0c242b266@illuminatedcomputing.com>
 <a37f180d-b54b-46d6-9e52-cc15f32be200@eisentraut.org> <596fefdd-6202-4228-b7c6-f240b1cbe06e@eisentraut.org>
 <df018b96-f0dc-49ba-98af-3a3983f683fe@illuminatedcomputing.com>
 <35400587-6865-4a2c-9ea4-4761669478ea@eisentraut.org> <71b45adb-b496-4e58-b134-0a091014e63f@illuminatedcomputing.com>
 <7be8724a-5c25-46d7-8325-1bd8be6fa523@eisentraut.org> <CACJufxHVg65raNhG2zBwXgjrD6jqace4NZbePyMhP8-_Q=iT8w@mail.gmail.com>
 <0af6a323-f026-4cd9-adf1-941038cd00de@illuminatedcomputing.com>
 <88518c81-dcdc-4c5b-9200-146b74a520ab@eisentraut.org> <113f5e25-4455-4bdb-98ec-56275705cc4e@illuminatedcomputing.com>
 <7bd1c8f9-a91a-41a3-990e-0f796ba692ec@eisentraut.org> <d1a793bd-d1f1-46d7-90b6-8b89b6feb1cc@illuminatedcomputing.com>
 <6c3edb2c-bc45-4f19-babd-8f4edf39b384@illuminatedcomputing.com>
 <41b57948-f4d3-455f-8880-e0ce7e61ffc1@illuminatedcomputing.com> <0d977c63-f4a6-43ba-aad0-35c06c55df11@eisentraut.org>
In-Reply-To: <0d977c63-f4a6-43ba-aad0-35c06c55df11@eisentraut.org>
From: jian he <jian.universality@gmail.com>
Date: Tue, 12 Mar 2024 10:45:21 +0800
Message-ID: <CACJufxG8H8fg7a=mgstpDq4cMKaJv9JEuWEXP8SooRc3wx3aEA@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Paul Jungwirth <pj@illuminatedcomputing.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CACJufxG8H8fg7a%3DmgstpDq4cMKaJv9JEuWEXP8SooRc3wx3aEA%40mail.gmail.com>
Precedence: bulk

+     <para>
+      If the last column is marked with <literal>PERIOD</literal>,
+      it is treated in a special way.
+      While the non-<literal>PERIOD</literal> columns are treated normally
+      (and there must be at least one of them),
+      the <literal>PERIOD</literal> column is not compared for equality.
+      Instead the constraint is considered satisfied
+      if the referenced table has matching records
+      (based on the non-<literal>PERIOD</literal> parts of the key)
+      whose combined <literal>PERIOD</literal> values completely cover
+      the referencing record's.
+      In other words, the reference must have a referent for its
entire duration.
+      Normally this column would be a range or multirange type,
+      although any type whose GiST opclass has a "contained by" operator
+      and a <literal>referenced_agg</literal> support function is allowed.
+      (See <xref linkend="gist-extensibility"/>.)
+      In addition the referenced table must have a primary key
+      or unique constraint declared with <literal>WITHOUT PORTION</literal>.
+     </para>

typo "referenced_agg", in the gist-extensibility.html page is "referencedagg"
<literal>WITHOUT PORTION</literal> should be <literal>WITHOUT OVERLAPS</literal>

+      While the non-<literal>PERIOD</literal> columns are treated normally
+      (and there must be at least one of them),
+      the <literal>PERIOD</literal> column is not compared for equality.
the above sentence didn't say what is "normally"?
maybe we can do the following:
+      While the non-<literal>PERIOD</literal> columns are treated
+ normally for equality
+      (and there must be at least one of them),
+      the <literal>PERIOD</literal> column is not compared for equality.


+<programlisting>
+Datum
+my_range_agg_transfn(PG_FUNCTION_ARGS)
+{
+    MemoryContext    aggContext;
+    Oid              rngtypoid;
+    ArrayBuildState *state;
+
+    if (!AggCheckCallContext(fcinfo, &amp;aggContext))
+        elog(ERROR, "range_agg_transfn called in non-aggregate context");
+
+    rngtypoid = get_fn_expr_argtype(fcinfo-&gt;flinfo, 1);
+    if (!type_is_range(rngtypoid))
+        elog(ERROR, "range_agg must be called with a range");
+
+    if (PG_ARGISNULL(0))
+        state = initArrayResult(rngtypoid, aggContext, false);
+    else
+        state = (ArrayBuildState *) PG_GETARG_POINTER(0);
+
+    /* skip NULLs */
+    if (!PG_ARGISNULL(1))
+        accumArrayResult(state, PG_GETARG_DATUM(1), false, rngtypoid,
aggContext);
+
+    PG_RETURN_POINTER(state);
+}
+
+Datum
+my_range_agg_finalfn(PG_FUNCTION_ARGS)
+{
+    MemoryContext    aggContext;
+    Oid              mltrngtypoid;
+    TypeCacheEntry  *typcache;
+    ArrayBuildState *state;
+    int32            range_count;
+    RangeType      **ranges;
+    int              i;
+
+    if (!AggCheckCallContext(fcinfo, &amp;aggContext))
+        elog(ERROR, "range_agg_finalfn called in non-aggregate context");
+
+    state = PG_ARGISNULL(0) ? NULL : (ArrayBuildState *) PG_GETARG_POINTER(0);
+    if (state == NULL)
+        /* This shouldn't be possible, but just in case.... */
+        PG_RETURN_NULL();
+
+    /* Also return NULL if we had zero inputs, like other aggregates */
+    range_count = state-&gt;nelems;
+    if (range_count == 0)
+        PG_RETURN_NULL();
+
+    mltrngtypoid = get_fn_expr_rettype(fcinfo-&gt;flinfo);
+    typcache = multirange_get_typcache(fcinfo, mltrngtypoid);
+
+    ranges = palloc0(range_count * sizeof(RangeType *));
+    for (i = 0; i &lt; range_count; i++)
+        ranges[i] = DatumGetRangeTypeP(state-&gt;dvalues[i]);
+
+    PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypoid,
typcache-&gt;rngtype, range_count, ranges));
+}

my_range_agg_transfn error message is inconsistent?
 `elog(ERROR, "range_agg_transfn called in non-aggregate context");`
`elog(ERROR, "range_agg must be called with a range");`
maybe just `my_range_agg_transfn`, instead of mention
{range_agg_transfn|range_agg}
similarly my_range_agg_finalfn error is also inconsistent.

my_range_agg_finalfn need  `type_is_multirange(mltrngtypoid)`?