public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v5 5/7] Row pattern recognition patch (docs).
3+ messages / 3 participants
[nested] [flat]
* [PATCH v5 5/7] Row pattern recognition patch (docs).
@ 2023-09-02 06:32 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Tatsuo Ishii @ 2023-09-02 06:32 UTC (permalink / raw)
---
doc/src/sgml/advanced.sgml | 52 ++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 +++++++++++++++++++++++--
3 files changed, 142 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..eda3612822 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -537,6 +537,58 @@ WHERE pos < 3;
<literal>rank</literal> less than 3.
</para>
+ <para>
+ Row pattern common syntax can be used with row pattern common syntax to
+ perform row pattern recognition in a query. Row pattern common syntax
+ includes two sub clauses. <literal>DEFINE</literal> defines definition
+ variables along with an expression. The expression must be a logical
+ expression, which means it must
+ return <literal>TRUE</literal>, <literal>FALSE</literal>
+ or <literal>NULL</literal>. Moreover if the expression comprises a column
+ reference, it must be the argument of <function>rpr</function>. An example
+ of <literal>DEFINE</literal> is as follows.
+
+<programlisting>
+DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+</programlisting>
+
+ Note that <function>PREV</function> returns price column in the previous
+ row if it's called in a context of row pattern recognition. So in the
+ second line means the definition variable "UP" is <literal>TRUE</literal>
+ when price column in the current row is greater than the price column in
+ the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when
+ price column in the current row is lower than the price column in the
+ previous row.
+ </para>
+ <para>
+ Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be
+ used. <literal>PATTERN</literal> defines a sequence of rows that satisfies
+ certain conditions. For example following <literal>PATTERN</literal>
+ defines that a row starts with the condition "LOWPRICE", then one or more
+ rows satisfy "UP" and finally one or more rows satisfy "DOWN". If a
+ sequence of rows found, rpr returns the column at the starting row.
+ Example of a <literal>SELECT</literal> using the <literal>DEFINE</literal>
+ and <literal>PATTERN</literal> clause is as follows.
+
+<programlisting>
+SELECT company, tdate, price, max(price) OVER w FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+</programlisting>
+ </para>
+
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7a0d4b9134..b7bfc9271e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21772,6 +21772,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -21811,6 +21812,59 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ Row pattern recognition navigation functions are listed in
+ <xref linkend="functions-rpr-navigation-table"/>. These functions
+ can be used to describe DEFINE clause of Row pattern recognition.
+ </para>
+
+ <table id="functions-rpr-navigation-table">
+ <title>Row Pattern Navigation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>prev</primary>
+ </indexterm>
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the previous row;
+ returns NULL if there is no previous row in the window frame.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>next</primary>
+ </indexterm>
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the next row;
+ returns NULL if there is no next row in the window frame.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</literal> or
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 0ee0cc7e64..8d3becd57a 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -966,8 +966,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
The <replaceable class="parameter">frame_clause</replaceable> can be one of
<synopsis>
-{ 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> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
</synopsis>
where <replaceable>frame_start</replaceable>
@@ -1074,6 +1074,40 @@ EXCLUDE NO OTHERS
a given peer group will be in the frame or excluded from it.
</para>
+ <para>
+ The
+ optional <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ defines the <firstterm>row pattern recognition condition</firstterm> for
+ this
+ window. <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST
+ ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls
+ how to proceed to next row position after a match
+ found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the
+ default) next row position is next to the last row of previous match. On
+ the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next
+ row position is always next to the last row of previous
+ match. <literal>DEFINE</literal> defines definition variables along with a
+ boolean expression. <literal>PATTERN</literal> defines a sequence of rows
+ that satisfies certain conditions using variables defined
+ in <literal>DEFINE</literal> clause. If the variable is not defined in
+ the <literal>DEFINE</literal> clause, it is implicitly assumed
+ following is defined in the <literal>DEFINE</literal> clause.
+
+<synopsis>
+<literal>variable_name</literal> AS TRUE
+</synopsis>
+
+ Note that the maximu number of variables defined
+ in <literal>DEFINE</literal> clause is 26.
+
+<synopsis>
+[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ]
+PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...]
+DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...]
+</synopsis>
+ </para>
+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
behavior of <firstterm>window functions</firstterm> appearing in the query's
--
2.25.1
----Next_Part(Sat_Sep__2_15_52_35_2023_273)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v5-0006-Row-pattern-recognition-patch-tests.patch"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Remove support for OpenSSL *eay32 libs on Windows
@ 2024-12-30 11:00 Daniel Gustafsson <[email protected]>
2024-12-31 00:49 ` Re: Remove support for OpenSSL *eay32 libs on Windows Michael Paquier <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Daniel Gustafsson @ 2024-12-30 11:00 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
> On 30 Dec 2024, at 02:24, Michael Paquier <[email protected]> wrote:
>
> On Fri, Dec 27, 2024 at 03:09:48PM +0100, Daniel Gustafsson wrote:
>> The thread in [0] which adds Meson support for *eay32 OpenSSL library names on
>> Windows reminded me that we should remove these from master since we no longer
>> support OpenSSL 1.0.2 (which was the final version with those names). Attached
>> is a small (as of yet untested on Windows/autoconf) diff removing these names
>> from the Makefiles.
>>
>> [0] CAHrt6656W9OnFomQTHBGYDcM5CKZ7hcgzFt8L+N0ezBZfcN3zA@mail.gmail.com
>
> Good idea, looks rather sane to me.
Thanks for looking.
> I don't have a clear idea about
> the WIN32/autoconf combination (sorry!), but I can't really believe
> that packagers of OpenSSL on Windows would want the same library name
> compatibility, patching upstream to be able to achieve the old naming.
I've never heard of anyone doing that, and it would only be additive leaving
the proper named files in place.
> fairywren, hamerkop and drongo use meson. Lorikeet relies on
> ./configure and builds with OpenSSL 3.0.12, so even there I would hope
> that we are in the clear.
All animals building HEAD would have to be well past the renaming since we
don't support version 1.0.2 there, so they should already be using the new
names.
> Not something I'm planning to care about, just noting that from the
> top of OpenSSL master branch (0958f5a5bc47 as of today):
> $ git grep eay32
> .gitignore:/ms/libeay32.def
> .gitignore:/ms/ssleay32.def
Nice catch, they should indeed not be needed anymore. I can submit a PR for it
if you don't want to deal with setting up a CLA.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Remove support for OpenSSL *eay32 libs on Windows
2024-12-30 11:00 Re: Remove support for OpenSSL *eay32 libs on Windows Daniel Gustafsson <[email protected]>
@ 2024-12-31 00:49 ` Michael Paquier <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Paquier @ 2024-12-31 00:49 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Mon, Dec 30, 2024 at 12:00:26PM +0100, Daniel Gustafsson wrote:
> On 30 Dec 2024, at 02:24, Michael Paquier <[email protected]> wrote:
>> Not something I'm planning to care about, just noting that from the
>> top of OpenSSL master branch (0958f5a5bc47 as of today):
>> $ git grep eay32
>> .gitignore:/ms/libeay32.def
>> .gitignore:/ms/ssleay32.def
>
> Nice catch, they should indeed not be needed anymore. I can submit a PR for it
> if you don't want to deal with setting up a CLA.
Thanks. I've not done this paperwork yet. Perhaps I should at some
point to submit a few things..
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-12-31 00:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-02 06:32 [PATCH v5 5/7] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]>
2024-12-30 11:00 Re: Remove support for OpenSSL *eay32 libs on Windows Daniel Gustafsson <[email protected]>
2024-12-31 00:49 ` Re: Remove support for OpenSSL *eay32 libs on Windows Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox