diff options
Diffstat (limited to 'doc/src/sgml/func.sgml')
| -rw-r--r-- | doc/src/sgml/func.sgml | 184 |
1 files changed, 176 insertions, 8 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b8be507f2df..084db0d40e5 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.369 2007/02/20 19:59:04 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.370 2007/03/20 05:44:59 neilc Exp $ --> <chapter id="functions"> <title>Functions and Operators</title> @@ -1468,18 +1468,53 @@ </row> <row> - <entry><literal><function>regexp_replace</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type> [,<parameter>flags</parameter> <type>text</type>])</literal></entry> + <entry><literal><function>regexp_matches</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type>])</literal></entry> + <entry><type>setof text[]</type></entry> + <entry> + Return all capture groups resulting from matching POSIX regular + expression against the <parameter>string</parameter>. See + <xref linkend="functions-posix-regexp"> for more information. + </entry> + <entry><literal>regexp_matches('foobarbequebaz', '(bar)(beque)')</literal></entry> + <entry><literal>{bar,beque}</literal></entry> + </row> + + <row> + <entry><literal><function>regexp_replace</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type>])</literal></entry> <entry><type>text</type></entry> <entry> Replace substring matching POSIX regular expression. See - <xref linkend="functions-matching"> for more information on pattern - matching. + <xref linkend="functions-posix-regexp"> for more information. </entry> <entry><literal>regexp_replace('Thomas', '.[mN]a.', 'M')</literal></entry> <entry><literal>ThM</literal></entry> </row> <row> + <entry><literal><function>regexp_split_to_array</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type> ])</literal></entry> + <entry><type>text[]</type></entry> + <entry> + Split <parameter>string</parameter> using POSIX regular expression as + the delimiter. See <xref linkend="functions-posix-regexp"> for more + information. + </entry> + <entry><literal>regexp_split_to_array('hello world', E'\\s+')</literal></entry> + <entry><literal>{hello,world}</literal></entry> + </row> + + <row> + <entry><literal><function>regexp_split_to_table</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type>])</literal></entry> + <entry><type>setof text</type></entry> + <entry> + Split <parameter>string</parameter> using POSIX regular expression as + the delimiter. See <xref linkend="functions-posix-regexp"> for more + information. + </entry> + <entry><literal>regexp_split_to_table('hello world', E'\\s+')</literal></entry> + <entry><literal>hello</literal><para><literal>world</literal></para> (2 rows)</entry> + </row> + + <row> <entry><literal><function>repeat</function>(<parameter>string</parameter> <type>text</type>, <parameter>number</parameter> <type>int</type>)</literal></entry> <entry><type>text</type></entry> <entry>Repeat <parameter>string</parameter> the specified @@ -2883,9 +2918,6 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation> <indexterm> <primary>substring</primary> </indexterm> - <indexterm> - <primary>regexp_replace</primary> - </indexterm> <synopsis> <replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional> @@ -3004,6 +3036,21 @@ substring('foobar' from '#"o_b#"%' for '#') <lineannotation>NULL</lineannotat <primary>regular expression</primary> <seealso>pattern matching</seealso> </indexterm> + <indexterm> + <primary>substring</primary> + </indexterm> + <indexterm> + <primary>regexp_replace</primary> + </indexterm> + <indexterm> + <primary>regexp_matches</primary> + </indexterm> + <indexterm> + <primary>regexp_split_to_table</primary> + </indexterm> + <indexterm> + <primary>regexp_split_to_array</primary> + </indexterm> <para> <xref linkend="functions-posix-table"> lists the available @@ -3134,7 +3181,10 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation> string containing zero or more single-letter flags that change the function's behavior. Flag <literal>i</> specifies case-insensitive matching, while flag <literal>g</> specifies replacement of each matching - substring rather than only the first one. + substring rather than only the first one. Other supported flags are + <literal>m</>, <literal>n</>, <literal>p</>, <literal>w</> and + <literal>x</>, whose meanings correspond to those shown in + <xref linkend="posix-embedded-options-table">. </para> <para> @@ -3149,6 +3199,124 @@ regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g') </programlisting> </para> + <para> + The <function>regexp_matches</> function returns all of the capture + groups resulting from matching a POSIX regular expression pattern. + It has the syntax + <function>regexp_matches</function>(<replaceable>string</>, <replaceable>pattern</> + <optional>, <replaceable>flags</> </optional>). + If there is no match to the <replaceable>pattern</>, the function returns no rows. + If there is a match, the function returns the contents of all of the capture groups + in a text array, or if there were no capture groups in the pattern, it returns the + contents of the entire match as a single-element text array. + The <replaceable>flags</> parameter is an optional text + string containing zero or more single-letter flags that change the + function's behavior. Flag <literal>i</> specifies case-insensitive + matching, while flag <literal>g</> causes the return of each matching + substring rather than only the first one. Other supported + flags are <literal>m</>, <literal>n</>, <literal>p</>, <literal>w</> and + <literal>x</>, whose meanings are described in + <xref linkend="posix-embedded-options-table">. + </para> + + <para> + Some examples: +<programlisting> +SELECT regexp_matches('foobarbequebaz', '(bar)(beque)'); + regexp_matches +---------------- + {bar,beque} +(1 row) + +SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g'); + regexp_matches +---------------- + {bar,beque} + {bazil,barf} +(2 rows) + +SELECT regexp_matches('foobarbequebaz', 'barbeque'); + regexp_matches +---------------- + {barbeque} +(1 row) +</programlisting> + </para> + + <para> + The <function>regexp_split_to_table</> function splits a string using a POSIX + regular expression pattern as a delimiter. It has the syntax + <function>regexp_split_to_table</function>(<replaceable>string</>, <replaceable>pattern</> + <optional>, <replaceable>flags</> </optional>). + If there is no match to the <replaceable>pattern</>, the function returns the + <replaceable>string</>. If there is at least one match, for each match it returns + the text from the end of the last match (or the beginning of the string) + to the beginning of the match. When there are no more matches, it + returns the text from the end of the last match to the end of the string. + The <replaceable>flags</> parameter is an optional text string containing + zero or more single-letter flags that change the function's behavior. + <function>regexp_split_to_table</function> supports the flags <literal>i</>, + <literal>m</>, <literal>n</>, <literal>p</>, <literal>w</> and + <literal>x</>, whose meanings are described in + <xref linkend="posix-embedded-options-table">. + </para> + + <para> + The <function>regexp_split_to_array</> function behaves the same as + <function>regexp_split_to_table</>, except that <function>regexp_split_to_array</> + returns its results as a <type>text[]</>. It has the syntax + <function>regexp_split_to_array</function>(<replaceable>string</>, <replaceable>pattern</> + <optional>, <replaceable>flags</> </optional>). + The parameters are the same as for <function>regexp_split_to_table</>. + </para> + + <para> + Some examples: +<programlisting> + +SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog', E'\\\s+') AS foo; + foo +-------- + the + quick + brown + fox + jumped + over + the + lazy + dog +(9 rows) + +SELECT regexp_split_to_array('the quick brown fox jumped over the lazy dog', E'\\s+'); + regexp_split_to_array +------------------------------------------------ + {the,quick,brown,fox,jumped,over,the,lazy,dog} +(1 row) + +SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo; + foo +----- + t + h + e + q + u + i + c + k + b + r + o + w + n + f + o + x +(16 rows) +</programlisting> + </para> + <para> <productname>PostgreSQL</productname>'s regular expressions are implemented using a package written by Henry Spencer. Much of |