Horton

Re: Row pattern recognition

Tatsuo Ishii <ishii@postgresql.org>

View thread
From: Tatsuo Ishii <ishii@postgresql.org>
To: david.g.johnston@gmail.com
Cc: vik@postgresfriends.org, jacob.champion@enterprisedb.com, pgsql-hackers@postgresql.org, er@xs4all.nl, peter@eisentraut.org
Date: 2024-10-25T04:04:53Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add temporal FOREIGN KEY contraints

  2. Remove obsolete executor cleanup code

Attachments

> On Tue, Oct 22, 2024 at 6:12 AM Vik Fearing <vik@postgresfriends.org> wrote:
> 
>>
>> On 22/10/2024 12:19, Tatsuo Ishii wrote:
>>
>> Acording to ISO/IEC 9075-2:2016 "4.21.2 Row pattern navigation operations",
>>
>>   <row pattern navigation operation> evaluates a <value expression> VE
>>   in a row NR, which may be different than current row CR.
>>
>> From this I think PREV(col + 1) should be interpreted as:
>>
>> 1. go to the previous row.
>> 2. evaluate "col + 1" at the current row (that was previous row).
>> 3. return the result.
>>
>> If my understanding is correct, prev(price + 1) has the same meaning
>> as prev(price) + 1.
>>
>>
>>
>> This is how I read the specification also.
>>
>>
>>
> That makes sense.  Definitely much nicer to only have to write PREV once if
> the expression you are evaluating involves multiple columns.  And is also
> consistent with window function "value" behavior.

Thanks to all who joined the discussion. I decided to support PREV and
NEXT in my RPR patches to allow to have multiple columns and other
expressions in their argument. e.g.

CREATE TEMP TABLE rpr1 (id INTEGER, i SERIAL, j INTEGER);
INSERT INTO rpr1(id, j) SELECT 1, g*2 FROM generate_series(1, 10) AS g;
SELECT id, i, j, count(*) OVER w
 FROM rpr1
 WINDOW w AS (
 PARTITION BY id
 ORDER BY i
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 AFTER MATCH SKIP PAST LAST ROW
 INITIAL
 PATTERN (START COND+)
 DEFINE
  START AS TRUE,
  COND AS PREV(i + j + 1) < 10
);
 id | i  | j  | count 
----+----+----+-------
  1 |  1 |  2 |     3
  1 |  2 |  4 |     0
  1 |  3 |  6 |     0
  1 |  4 |  8 |     0
  1 |  5 | 10 |     0
  1 |  6 | 12 |     0
  1 |  7 | 14 |     0
  1 |  8 | 16 |     0
  1 |  9 | 18 |     0
  1 | 10 | 20 |     0
(10 rows)

Attached are the v23 patches. V23 also includes the fix for the problem
pointed out by Jacob Champion and test cases from him. Thank you, Jacob.
https://www.postgresql.org/message-id/CAOYmi%2Bns3kHjC83ap_BCfJCL0wfO5BJ_sEByOEpgNOrsPhqQTg%40mail.gmail.com

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp