507
507
<entry> <literal>^</literal> </entry>
508
<entry>exponentiation</entry>
508
<entry>exponentiation (associates left to right)</entry>
509
509
<entry><literal>2.0 ^ 3.0</literal></entry>
510
510
<entry><literal>8</literal></entry>
3574
While most regular-expression searches can be executed very quickly,
3575
regular expressions can be contrived that take arbitrary amounts of
3576
time and memory to process. Be wary of accepting regular-expression
3577
search patterns from hostile sources. If you must do so, it is
3578
advisable to impose a statement timeout.
3582
Searches using <function>SIMILAR TO</function> patterns have the same
3583
security hazards, since <function>SIMILAR TO</function> provides many
3584
of the same capabilities as <acronym>POSIX</acronym>-style regular
3589
<function>LIKE</function> searches, being much simpler than the other
3590
two options, are safer to use with possibly-hostile pattern sources.
3572
3594
<sect2 id="functions-like">
3573
3595
<title><function>LIKE</function></title>
4636
4658
<entry> <literal>\e</> </entry>
4637
4659
<entry> the character whose collating-sequence name
4638
4660
is <literal>ESC</>,
4639
or failing that, the character with octal value 033 </entry>
4661
or failing that, the character with octal value <literal>033</> </entry>
4663
4685
<entry> <literal>\u</><replaceable>wxyz</> </entry>
4664
4686
<entry> (where <replaceable>wxyz</> is exactly four hexadecimal digits)
4665
the UTF16 (Unicode, 16-bit) character <literal>U+</><replaceable>wxyz</>
4666
in the local byte ordering </entry>
4687
the character whose hexadecimal value is
4688
<literal>0x</><replaceable>wxyz</>
4670
4693
<entry> <literal>\U</><replaceable>stuvwxyz</> </entry>
4671
4694
<entry> (where <replaceable>stuvwxyz</> is exactly eight hexadecimal
4673
reserved for a hypothetical Unicode extension to 32 bits
4696
the character whose hexadecimal value is
4697
<literal>0x</><replaceable>stuvwxyz</>
4747
Numeric character-entry escapes specifying values outside the ASCII range
4748
(0-127) have meanings dependent on the database encoding. When the
4749
encoding is UTF-8, escape values are equivalent to Unicode code points,
4750
for example <literal>\u1234</> means the character <literal>U+1234</>.
4751
For other multibyte encodings, character-entry escapes usually just
4752
specify the concatenation of the byte values for the character. If the
4753
escape value does not correspond to any legal character in the database
4754
encoding, no error will be raised, but it will never match any data.
4723
4758
The character-entry escapes are always taken as ordinary characters.
4724
4759
For example, <literal>\135</> is <literal>]</> in ASCII, but
4725
4760
<literal>\135</> does not terminate a bracket expression.
5170
5205
The quantifiers <literal>{1,1}</> and <literal>{1,1}?</>
5171
5206
can be used to force greediness or non-greediness, respectively,
5172
5207
on a subexpression or a whole RE.
5208
This is useful when you need the whole RE to have a greediness attribute
5209
different from what's deduced from its elements. As an example,
5210
suppose that we are trying to separate a string containing some digits
5211
into the digits and the parts before and after them. We might try to
5214
SELECT regexp_matches('abc01234xyz', '(.*)(\d+)(.*)');
5215
<lineannotation>Result: </lineannotation><computeroutput>{abc0123,4,xyz}</computeroutput>
5217
That didn't work: the first <literal>.*</> is greedy so
5218
it <quote>eats</> as much as it can, leaving the <literal>\d+</> to
5219
match at the last possible place, the last digit. We might try to fix
5220
that by making it non-greedy:
5222
SELECT regexp_matches('abc01234xyz', '(.*?)(\d+)(.*)');
5223
<lineannotation>Result: </lineannotation><computeroutput>{abc,0,""}</computeroutput>
5225
That didn't work either, because now the RE as a whole is non-greedy
5226
and so it ends the overall match as soon as possible. We can get what
5227
we want by forcing the RE as a whole to be greedy:
5229
SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
5230
<lineannotation>Result: </lineannotation><computeroutput>{abc,01234,xyz}</computeroutput>
5232
Controlling the RE's overall greediness separately from its components'
5233
greediness allows great flexibility in handling variable-length patterns.
5176
Match lengths are measured in characters, not collating elements.
5237
When deciding what is a longer or shorter match,
5238
match lengths are measured in characters, not collating elements.
5177
5239
An empty string is considered longer than no match at all.
5179
5241
<literal>bb*</>
5696
5758
<entry><literal>FM</literal> prefix</entry>
5697
<entry>fill mode (suppress padding blanks and trailing zeroes)</entry>
5759
<entry>fill mode (suppress leading zeroes and padding blanks)</entry>
5698
5760
<entry><literal>FMMonth</literal></entry>
6083
6145
<entry><literal>FM</literal> prefix</entry>
6084
<entry>fill mode (suppress padding blanks and trailing zeroes)</entry>
6146
<entry>fill mode (suppress leading zeroes and padding blanks)</entry>
6085
6147
<entry><literal>FM9999</literal></entry>
6221
6283
<entry><literal>to_char(485, 'L999')</literal></entry>
6222
<entry><literal>'DM 485</literal></entry>
6284
<entry><literal>'DM 485'</literal></entry>
6225
6287
<entry><literal>to_char(485, 'RN')</literal></entry>
12316
12378
<primary>lag</primary>
12319
lag(<replaceable class="parameter">value</replaceable> <type>any</>
12381
lag(<replaceable class="parameter">value</replaceable> <type>anyelement</>
12320
12382
[, <replaceable class="parameter">offset</replaceable> <type>integer</>
12321
[, <replaceable class="parameter">default</replaceable> <type>any</> ]])
12383
[, <replaceable class="parameter">default</replaceable> <type>anyelement</> ]])
12328
12390
returns <replaceable class="parameter">value</replaceable> evaluated at
12329
12391
the row that is <replaceable class="parameter">offset</replaceable>
12330
12392
rows before the current row within the partition; if there is no such
12331
row, instead return <replaceable class="parameter">default</replaceable>.
12393
row, instead return <replaceable class="parameter">default</replaceable>
12394
(which must be of the same type as
12395
<replaceable class="parameter">value</replaceable>).
12332
12396
Both <replaceable class="parameter">offset</replaceable> and
12333
12397
<replaceable class="parameter">default</replaceable> are evaluated
12334
12398
with respect to the current row. If omitted,
12343
12407
<primary>lead</primary>
12346
lead(<replaceable class="parameter">value</replaceable> <type>any</>
12410
lead(<replaceable class="parameter">value</replaceable> <type>anyelement</>
12347
12411
[, <replaceable class="parameter">offset</replaceable> <type>integer</>
12348
[, <replaceable class="parameter">default</replaceable> <type>any</> ]])
12412
[, <replaceable class="parameter">default</replaceable> <type>anyelement</> ]])
12355
12419
returns <replaceable class="parameter">value</replaceable> evaluated at
12356
12420
the row that is <replaceable class="parameter">offset</replaceable>
12357
12421
rows after the current row within the partition; if there is no such
12358
row, instead return <replaceable class="parameter">default</replaceable>.
12422
row, instead return <replaceable class="parameter">default</replaceable>
12423
(which must be of the same type as
12424
<replaceable class="parameter">value</replaceable>).
12359
12425
Both <replaceable class="parameter">offset</replaceable> and
12360
12426
<replaceable class="parameter">default</replaceable> are evaluated
12361
12427
with respect to the current row. If omitted,
added
removed
doc/src/sgml/func.sgml
