Skip to content

Commit 222b697

Browse files
peterepetere
committed
doc: More documentation on regular expressions and SQL standard
Reviewed-by: Gilles Darold <gilles@darold.net> Discussion: https://www.postgresql.org/message-id/b7988566-daa2-80ed-2fdc-6f6630462d26@enterprisedb.com
1 parent b1c169c commit 222b697

File tree

2 files changed

+88
-13
lines changed

2 files changed

+88
-13
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 83 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -7353,27 +7353,102 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
73537353
<!-- end re_syntax.n man page -->
73547354

73557355
<sect3 id="posix-vs-xquery">
7356-
<title>Differences from XQuery (<literal>LIKE_REGEX</literal>)</title>
7356+
<title>Differences from SQL Standard and XQuery</title>
73577357

73587358
<indexterm zone="posix-vs-xquery">
7359-
<primary><literal>LIKE_REGEX</literal></primary>
7359+
<primary>LIKE_REGEX</primary>
7360+
</indexterm>
7361+
7362+
<indexterm zone="posix-vs-xquery">
7363+
<primary>OCCURRENCES_REGEX</primary>
7364+
</indexterm>
7365+
7366+
<indexterm zone="posix-vs-xquery">
7367+
<primary>POSITION_REGEX</primary>
7368+
</indexterm>
7369+
7370+
<indexterm zone="posix-vs-xquery">
7371+
<primary>SUBSTRING_REGEX</primary>
7372+
</indexterm>
7373+
7374+
<indexterm zone="posix-vs-xquery">
7375+
<primary>TRANSLATE_REGEX</primary>
73607376
</indexterm>
73617377

73627378
<indexterm zone="posix-vs-xquery">
73637379
<primary>XQuery regular expressions</primary>
73647380
</indexterm>
73657381

73667382
<para>
7367-
Since SQL:2008, the SQL standard includes
7368-
a <literal>LIKE_REGEX</literal> operator that performs pattern
7383+
Since SQL:2008, the SQL standard includes regular expression operators
7384+
and functions that performs pattern
73697385
matching according to the XQuery regular expression
7370-
standard. <productname>PostgreSQL</productname> does not yet
7371-
implement this operator, but you can get very similar behavior using
7372-
the <function>regexp_match()</function> function, since XQuery
7373-
regular expressions are quite close to the ARE syntax described above.
7386+
standard:
7387+
<itemizedlist>
7388+
<listitem><para><literal>LIKE_REGEX</literal></para></listitem>
7389+
<listitem><para><literal>OCCURRENCES_REGEX</literal></para></listitem>
7390+
<listitem><para><literal>POSITION_REGEX</literal></para></listitem>
7391+
<listitem><para><literal>SUBSTRING_REGEX</literal></para></listitem>
7392+
<listitem><para><literal>TRANSLATE_REGEX</literal></para></listitem>
7393+
</itemizedlist>
7394+
<productname>PostgreSQL</productname> does not currently implement these
7395+
operators and functions. You can get approximately equivalent
7396+
functionality in each case as shown in <xref
7397+
linkend="functions-regexp-sql-table"/>. (Various optional clauses on
7398+
both sides have been omitted in this table.)
7399+
</para>
7400+
7401+
<table id="functions-regexp-sql-table">
7402+
<title>Regular Expression Functions Equivalencies</title>
7403+
7404+
<tgroup cols="2">
7405+
<thead>
7406+
<row>
7407+
<entry>SQL standard</entry>
7408+
<entry>PostgreSQL</entry>
7409+
</row>
7410+
</thead>
7411+
7412+
<tbody>
7413+
<row>
7414+
<entry><literal><replaceable>string</replaceable> LIKE_REGEX <replaceable>pattern</replaceable></literal></entry>
7415+
<entry><literal>regexp_like(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal> or <literal><replaceable>string</replaceable> ~ <replaceable>pattern</replaceable></literal></entry>
7416+
</row>
7417+
7418+
<row>
7419+
<entry><literal>OCCURRENCES_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable></literal></entry>
7420+
<entry><literal>regexp_count(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal></entry>
7421+
</row>
7422+
7423+
<row>
7424+
<entry><literal>POSITION_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable></literal></entry>
7425+
<entry><literal>regexp_instr(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal></entry>
7426+
</row>
7427+
7428+
<row>
7429+
<entry><literal>SUBSTRING_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable></literal></entry>
7430+
<entry><literal>regexp_substr(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal></entry>
7431+
</row>
7432+
7433+
<row>
7434+
<entry><literal>TRANSLATE_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable> WITH <replaceable>replacement</replaceable></literal></entry>
7435+
<entry><literal>regexp_replace(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>replacement</replaceable>)</literal></entry>
7436+
</row>
7437+
</tbody>
7438+
</tgroup>
7439+
</table>
7440+
7441+
<para>
7442+
Regular expression functions similar to those provided by PostgreSQL are
7443+
also available in a number of other SQL implementations, whereas the
7444+
SQL-standard functions are not as widely implemented. Some of the
7445+
details of the regular expression syntax will likely differ in each
7446+
implementation.
73747447
</para>
73757448

73767449
<para>
7450+
The SQL-standard operators and functions use XQuery regular expressions,
7451+
which are quite close to the ARE syntax described above.
73777452
Notable differences between the existing POSIX-based
73787453
regular-expression feature and XQuery regular expressions include:
73797454

‎src/backend/catalog/sql_features.txt

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -323,11 +323,11 @@ F821 Local table references NO
323323
F831 Full cursor update NO
324324
F831 Full cursor update 01 Updatable scrollable cursors NO
325325
F831 Full cursor update 02 Updatable ordered cursors NO
326-
F841 LIKE_REGEX predicate NO
327-
F842 OCCURRENCES_REGEX function NO
328-
F843 POSITION_REGEX function NO
329-
F844 SUBSTRING_REGEX function NO
330-
F845 TRANSLATE_REGEX function NO
326+
F841 LIKE_REGEX predicate NO consider regexp_like()
327+
F842 OCCURRENCES_REGEX function NO consider regexp_matches()
328+
F843 POSITION_REGEX function NO consider regexp_instr()
329+
F844 SUBSTRING_REGEX function NO consider regexp_substr()
330+
F845 TRANSLATE_REGEX function NO consider regexp_replace()
331331
F846 Octet support in regular expression operators NO
332332
F847 Nonconstant regular expressions NO
333333
F850 Top-level <order by clause> in <query expression> YES

0 commit comments

Comments
 (0)