- publishing free software manuals
PostgreSQL Reference Manual - Volume 1 - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
ISBN 0954612027
RRP £32.00 ($49.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

7.7.3.4 Regular Expression Metasyntax

In addition to the main syntax described above, there are some special forms and miscellaneous syntactic facilities available.

Normally the flavor of RE being used is determined by regex_flavor. However, this can be overridden by a director prefix. If an RE begins with ***:, the rest of the RE is taken as an ARE regardless of regex_flavor. If an RE begins with ***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters.

An ARE may begin with embedded options: a sequence (?xyz) (where xyz is one or more alphabetic characters) specifies options affecting the rest of the RE. These options override any previously determined options (including both the RE flavor and case sensitivity). The available option letters are shown in Table 7-19.

Table 7-19: ARE Embedded-Option Letters
Option Description
b rest of RE is a BRE
c case-sensitive matching (overrides operator type)
e rest of RE is an ERE
i case-insensitive matching (see section 7.7.3.5 Regular Expression Matching Rules) (overrides operator type)
m historical synonym for n
n newline-sensitive matching (see section 7.7.3.5 Regular Expression Matching Rules)
p partial newline-sensitive matching (see section 7.7.3.5 Regular Expression Matching Rules)
q rest of RE is a literal (“quoted”) string, all ordinary characters
s non-newline-sensitive matching (default)
t tight syntax (default; see below)
w inverse partial newline-sensitive (“weird”) matching (see section 7.7.3.5 Regular Expression Matching Rules)
x expanded syntax (see below)

Embedded options take effect at the ) terminating the sequence. They may appear only at the start of an ARE (after the ***: director if any).

In addition to the usual (tight) RE syntax, in which all characters are significant, there is an expanded syntax, available by specifying the embedded x option. In the expanded syntax, white-space characters in the RE are ignored, as are all characters between a # and the following newline (or the end of the RE). This permits paragraphing and commenting a complex RE. There are three exceptions to that basic rule:

For this purpose, white-space characters are blank, tab, newline, and any character that belongs to the space character class.

Finally, in an ARE, outside bracket expressions, the sequence (?#ttt) (where ttt is any text not containing a )) is a comment, completely ignored. Again, this is not allowed between the characters of multicharacter symbols, like (?:. Such comments are more a historical artifact than a useful facility, and their use is deprecated; use the expanded syntax instead.

None of these metasyntax extensions is available if an initial ***= director has specified that the user's input be treated as a literal string rather than as an RE.

ISBN 0954612027PostgreSQL Reference Manual - Volume 1 - SQL Language ReferenceSee the print edition