Re: Row pattern recognition - Mailing list pgsql-hackers

> On 7/22/23 03:11, Tatsuo Ishii wrote:
>>>>> Maybe
>>>>> that can help clarify the design? It feels like it'll need to
>>>>> eventually
>>>>> be a "real" function that operates on the window state, even if it
>>>>> doesn't support all of the possible complexities in v1.
>>>> Unfortunately an window function can not call other window functions.
>>> Can that restriction be lifted for the EXPR_KIND_RPR_DEFINE case?
> 
>> I am not sure at this point. Current PostgreSQL executor creates
>> WindowStatePerFuncData for each window function and aggregate
>> appearing in OVER clause. This means PREV/NEXT and other row pattern
>> navigation operators cannot have their own WindowStatePerFuncData if
>> they do not appear in OVER clauses in a query even if PREV/NEXT
>> etc. are defined as window function.
>> 
>>> Or
>>> does it make sense to split the pattern navigation "functions" into
>>> their own new concept, and maybe borrow some of the window function
>>> infrastructure for it?
> 
>> Maybe. Suppose a window function executes row pattern matching using
>> price > PREV(price). The window function already receives
>> WindowStatePerFuncData. If we can pass the WindowStatePerFuncData to
>> PREV, we could let PREV do the real work (getting previous tuple).
>> I have not tried this yet, though.
> 
> 
> I don't understand this logic.  Window functions work over a window
> frame.

Yes.

> What we are talking about here is *defining* a window
> frame.

Well, we are defining a "reduced" window frame within a (full) window
frame. A "reduced" window frame is calculated each time when a window
function is called.

> How can a window function execute row pattern matching?

A window function is called for each row fed by an outer plan. It
fetches current, previous and next row to execute pattern matching. If
it matches, the window function moves to next row and repeat the
process, until pattern match fails.

Below is an example window function to execute pattern matching (I
will include this in the v3 patch). row_is_in_reduced_frame() is a
function to execute pattern matching. It returns the number of rows in
the reduced frame if pattern match succeeds. If succeeds, the function
returns the last row in the reduced frame instead of the last row in
the full window frame.

/*
 * last_value
 * return the value of VE evaluated on the last row of the
 * window frame, per spec.
 */
Datum
window_last_value(PG_FUNCTION_ARGS)
{
    WindowObject winobj = PG_WINDOW_OBJECT();
    Datum        result;
    bool        isnull;
    int64        abspos;
    int            num_reduced_frame;

    abspos = WinGetCurrentPosition(winobj);
    num_reduced_frame = row_is_in_reduced_frame(winobj, abspos);

    if (num_reduced_frame == 0)
        /* no RPR is involved */
        result = WinGetFuncArgInFrame(winobj, 0,
                                      0, WINDOW_SEEK_TAIL, true,
                                      &isnull, NULL);
    else if (num_reduced_frame > 0)
        /* get last row value in the reduced frame */
        result = WinGetFuncArgInFrame(winobj, 0,
                                      num_reduced_frame - 1, WINDOW_SEEK_HEAD, true,
                                      &isnull, NULL);
    else
        /* RPR is involved and current row is unmatched or skipped */
        isnull = true;

    if (isnull)
        PG_RETURN_NULL();

    PG_RETURN_DATUM(result);
}