On 03.08.21 19:10, Tom Lane wrote:
Gilles Darold <gil...@darold.net> writes:
Sorry I have missed that, but I'm fine with this implemenation so let's
keep the v6 version of the patch and drop this one.
Pushed, then. There's still lots of time to tweak the behavior of course.
I have a documentation follow-up to this. It seems that these new
functions are almost a de facto standard, whereas the SQL-standard
functions are not implemented anywhere. I propose the attached patch to
update the subsection in the pattern-matching section to give more
detail on this and suggest equivalent functions among these newly added
ones. What do you think?From a2dbd0e24a30b945a5d641ed773dc44f5e6b50c1 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 15 Dec 2021 11:02:59 +0100
Subject: [PATCH] doc: More documentation on regular expressions and SQL
standard
---
doc/src/sgml/func.sgml | 91 +++++++++++++++++++++++++---
src/backend/catalog/sql_features.txt | 10 +--
2 files changed, 88 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5801299b27..e58efce586 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7353,10 +7353,26 @@ <title>Basic Regular Expressions</title>
<!-- end re_syntax.n man page -->
<sect3 id="posix-vs-xquery">
- <title>Differences from XQuery (<literal>LIKE_REGEX</literal>)</title>
+ <title>Differences from SQL Standard and XQuery</title>
<indexterm zone="posix-vs-xquery">
- <primary><literal>LIKE_REGEX</literal></primary>
+ <primary>LIKE_REGEX</primary>
+ </indexterm>
+
+ <indexterm zone="posix-vs-xquery">
+ <primary>OCCURRENCES_REGEX</primary>
+ </indexterm>
+
+ <indexterm zone="posix-vs-xquery">
+ <primary>POSITION_REGEX</primary>
+ </indexterm>
+
+ <indexterm zone="posix-vs-xquery">
+ <primary>SUBSTRING_REGEX</primary>
+ </indexterm>
+
+ <indexterm zone="posix-vs-xquery">
+ <primary>TRANSLATE_REGEX</primary>
</indexterm>
<indexterm zone="posix-vs-xquery">
@@ -7364,16 +7380,75 @@ <title>Differences from XQuery
(<literal>LIKE_REGEX</literal>)</title>
</indexterm>
<para>
- Since SQL:2008, the SQL standard includes
- a <literal>LIKE_REGEX</literal> operator that performs pattern
+ Since SQL:2008, the SQL standard includes regular expression operators
+ and functions that performs pattern
matching according to the XQuery regular expression
- standard. <productname>PostgreSQL</productname> does not yet
- implement this operator, but you can get very similar behavior using
- the <function>regexp_match()</function> function, since XQuery
- regular expressions are quite close to the ARE syntax described above.
+ standard:
+ <itemizedlist>
+ <listitem><para><literal>LIKE_REGEX</literal></para></listitem>
+ <listitem><para><literal>OCCURRENCES_REGEX</literal></para></listitem>
+ <listitem><para><literal>POSITION_REGEX</literal></para></listitem>
+ <listitem><para><literal>SUBSTRING_REGEX</literal></para></listitem>
+ <listitem><para><literal>TRANSLATE_REGEX</literal></para></listitem>
+ </itemizedlist>
+ <productname>PostgreSQL</productname> does not currently implement these
+ operators and functions. You can get approximately equivalent
+ functionality in each case as shown in <xref
+ linkend="functions-regexp-sql-table"/>. (Various optional clauses on
+ both sides have been omitted in this table.)
+ </para>
+
+ <table id="functions-regexp-sql-table">
+ <title>Regular Expression Functions Equivalencies</title>
+
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>SQL standard</entry>
+ <entry>PostgreSQL</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal><replaceable>string</replaceable> LIKE_REGEX
<replaceable>pattern</replaceable></literal></entry>
+ <entry><literal>regexp_like(<replaceable>string</replaceable>,
<replaceable>pattern</replaceable>)</literal> or
<literal><replaceable>string</replaceable> ~
<replaceable>pattern</replaceable></literal></entry>
+ </row>
+
+ <row>
+ <entry><literal>OCCURRENCES_REGEX(<replaceable>pattern</replaceable>
IN <replaceable>string</replaceable></literal></entry>
+ <entry><literal>regexp_count(<replaceable>string</replaceable>,
<replaceable>pattern</replaceable>)</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal>POSITION_REGEX(<replaceable>pattern</replaceable> IN
<replaceable>string</replaceable></literal></entry>
+ <entry><literal>regexp_instr(<replaceable>string</replaceable>,
<replaceable>pattern</replaceable>)</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal>SUBSTRING_REGEX(<replaceable>pattern</replaceable> IN
<replaceable>string</replaceable></literal></entry>
+ <entry><literal>regexp_substr(<replaceable>string</replaceable>,
<replaceable>pattern</replaceable>)</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal>TRANSLATE_REGEX(<replaceable>pattern</replaceable> IN
<replaceable>string</replaceable> WITH
<replaceable>replacement</replaceable></literal></entry>
+ <entry><literal>regexp_replace(<replaceable>string</replaceable>,
<replaceable>pattern</replaceable>,
<replaceable>replacement</replaceable>)</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Regular expression functions similar to those provided by PostgreSQL are
+ also available in a number of other SQL implementations, whereas the
+ SQL-standard functions are not as widely implemented. Some of the
+ details of the regular expression syntax will likely differ in each
+ implementation.
</para>
<para>
+ The SQL-standard operators and functions use XQuery regular expressions,
+ which are quite close to the ARE syntax described above.
Notable differences between the existing POSIX-based
regular-expression feature and XQuery regular expressions include:
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index 9f424216e2..b8a78f4d41 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -323,11 +323,11 @@ F821 Local table references NO
F831 Full cursor update NO
F831 Full cursor update 01 Updatable scrollable cursors NO
F831 Full cursor update 02 Updatable ordered cursors NO
-F841 LIKE_REGEX predicate NO
-F842 OCCURRENCES_REGEX function NO
-F843 POSITION_REGEX function NO
-F844 SUBSTRING_REGEX function NO
-F845 TRANSLATE_REGEX function NO
+F841 LIKE_REGEX predicate NO consider regexp_like()
+F842 OCCURRENCES_REGEX function NO consider
regexp_matches()
+F843 POSITION_REGEX function NO consider regexp_instr()
+F844 SUBSTRING_REGEX function NO consider
regexp_substr()
+F845 TRANSLATE_REGEX function NO consider
regexp_replace()
F846 Octet support in regular expression operators NO
F847 Nonconstant regular expressions NO
F850 Top-level <order by clause> in <query expression>
YES
--
2.34.1
