Skip to content

Commit 580f872

Browse files
tglsfdctglsfdc
committed
Add argument names to the regexp_XXX functions.
This change allows these functions to be called using named-argument notation, which can be helpful for readability, particularly for the ones with many arguments. There was considerable debate about exactly which names to use, but in the end we settled on the names already shown in our documentation table 9.10. The citext extension provides citext-aware versions of some of these functions, so add argument names to those too. In passing, fix table 9.10's syntax synopses for regexp_match, which were slightly wrong about which combinations of arguments are allowed. Jian He, reviewed by Dian Fay and others Discussion: https://postgr.es/m/CACJufxG3NFKKsh6x4fRLv8h3V-HvN4W5dA=zNKMxsNcDwOKang@mail.gmail.com
1 parent 05faf06 commit 580f872

File tree

7 files changed

+112
-32
lines changed

7 files changed

+112
-32
lines changed

‎contrib/citext/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@ MODULES = citext
44

55
EXTENSION = citext
66
DATA = citext--1.4.sql \
7+
citext--1.6--1.7.sql \
78
citext--1.5--1.6.sql \
89
citext--1.4--1.5.sql \
910
citext--1.3--1.4.sql \

‎contrib/citext/citext--1.6--1.7.sql

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
/* contrib/citext/citext--1.6--1.7.sql */
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use "ALTER EXTENSION citext UPDATE TO '1.7'" to load this file. \quit
5+
6+
-- add function argument names
7+
CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) RETURNS TEXT[] AS $$
8+
SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
9+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
10+
11+
CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext, flags text) RETURNS TEXT[] AS $$
12+
SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
13+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
14+
15+
CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext) RETURNS SETOF TEXT[] AS $$
16+
SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
17+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1;
18+
19+
CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext, flags text) RETURNS SETOF TEXT[] AS $$
20+
SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
21+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10;
22+
23+
CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text) returns TEXT AS $$
24+
SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
25+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
26+
27+
CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text, flags text) returns TEXT AS $$
28+
SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN $4 || 'i' ELSE $4 END);
29+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
30+
31+
CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext) RETURNS TEXT[] AS $$
32+
SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
33+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
34+
35+
CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext, flags text) RETURNS TEXT[] AS $$
36+
SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
37+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
38+
39+
CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext) RETURNS SETOF TEXT AS $$
40+
SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
41+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
42+
43+
CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext, flags text) RETURNS SETOF TEXT AS $$
44+
SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
45+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

‎contrib/citext/citext.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# citext extension
22
comment = 'data type for case-insensitive character strings'
3-
default_version = '1.6'
3+
default_version = '1.7'
44
module_pathname = '$libdir/citext'
55
relocatable = true
66
trusted = true

‎contrib/citext/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ install_data(
2525
'citext--1.4.sql',
2626
'citext--1.4--1.5.sql',
2727
'citext--1.5--1.6.sql',
28+
'citext--1.6--1.7.sql',
2829
kwargs: contrib_data_args,
2930
)
3031

‎doc/src/sgml/func.sgml

Lines changed: 16 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -3426,7 +3426,6 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
34263426
<primary>regexp_replace</primary>
34273427
</indexterm>
34283428
<function>regexp_replace</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type>
3429-
[, <parameter>start</parameter> <type>integer</type> ]
34303429
[, <parameter>flags</parameter> <type>text</type> ] )
34313430
<returnvalue>text</returnvalue>
34323431
</para>
@@ -3445,20 +3444,27 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
34453444
<row>
34463445
<entry role="func_table_entry"><para role="func_signature">
34473446
<function>regexp_replace</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type>,
3448-
<parameter>start</parameter> <type>integer</type>,
3449-
<parameter>N</parameter> <type>integer</type>
3450-
[, <parameter>flags</parameter> <type>text</type> ] )
3447+
<parameter>start</parameter> <type>integer</type>
3448+
[, <parameter>N</parameter> <type>integer</type>
3449+
[, <parameter>flags</parameter> <type>text</type> ] ] )
34513450
<returnvalue>text</returnvalue>
34523451
</para>
34533452
<para>
34543453
Replaces the substring that is the <parameter>N</parameter>'th
34553454
match to the POSIX regular expression <parameter>pattern</parameter>,
3456-
or all such matches if <parameter>N</parameter> is zero; see
3455+
or all such matches if <parameter>N</parameter> is zero, with the
3456+
search beginning at the <parameter>start</parameter>'th character
3457+
of <parameter>string</parameter>. If <parameter>N</parameter> is
3458+
omitted, it defaults to 1. See
34573459
<xref linkend="functions-posix-regexp"/>.
34583460
</para>
34593461
<para>
34603462
<literal>regexp_replace('Thomas', '.', 'X', 3, 2)</literal>
34613463
<returnvalue>ThoXas</returnvalue>
3464+
</para>
3465+
<para>
3466+
<literal>regexp_replace(string=>'hello world', pattern=>'l', replacement=>'XX', start=>1, "N"=>2)</literal>
3467+
<returnvalue>helXXo world</returnvalue>
34623468
</para></entry>
34633469
</row>
34643470

@@ -5963,7 +5969,7 @@ regexp_count('ABCABCAXYaxy', 'A.', 1, 'i') <lineannotation>4</lineannotation>
59635969
<programlisting>
59645970
regexp_instr('number of your street, town zip, FR', '[^,]+', 1, 2)
59655971
<lineannotation>23</lineannotation>
5966-
regexp_instr('ABCDEFGHI', '(c..)(...)', 1, 1, 0, 'i', 2)
5972+
regexp_instr(string=>'ABCDEFGHI', pattern=>'(c..)(...)', start=>1, "N"=>1, endoption=>0, flags=>'i', subexpr=>2)
59675973
<lineannotation>6</lineannotation>
59685974
</programlisting>
59695975
</para>
@@ -6109,7 +6115,7 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
61096115
The <function>regexp_replace</function> function provides substitution of
61106116
new text for substrings that match POSIX regular expression patterns.
61116117
It has the syntax
6112-
<function>regexp_replace</function>(<replaceable>source</replaceable>,
6118+
<function>regexp_replace</function>(<replaceable>string</replaceable>,
61136119
<replaceable>pattern</replaceable>, <replaceable>replacement</replaceable>
61146120
<optional>, <replaceable>start</replaceable>
61156121
<optional>, <replaceable>N</replaceable>
@@ -6118,9 +6124,9 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
61186124
(Notice that <replaceable>N</replaceable> cannot be specified
61196125
unless <replaceable>start</replaceable> is,
61206126
but <replaceable>flags</replaceable> can be given in any case.)
6121-
The <replaceable>source</replaceable> string is returned unchanged if
6127+
The source <replaceable>string</replaceable> is returned unchanged if
61226128
there is no match to the <replaceable>pattern</replaceable>. If there is a
6123-
match, the <replaceable>source</replaceable> string is returned with the
6129+
match, the <replaceable>string</replaceable> is returned with the
61246130
<replaceable>replacement</replaceable> string substituted for the matching
61256131
substring. The <replaceable>replacement</replaceable> string can contain
61266132
<literal>\</literal><replaceable>n</replaceable>, where <replaceable>n</replaceable> is 1
@@ -6161,7 +6167,7 @@ regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
61616167
<lineannotation>fooXarYXazY</lineannotation>
61626168
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i')
61636169
<lineannotation>X PXstgrXSQL fXnctXXn</lineannotation>
6164-
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i')
6170+
regexp_replace(string=>'A PostgreSQL function', pattern=>'a|e|i|o|u', replacement=>'X', start=>1, "N"=>3, flags=>'i')
61656171
<lineannotation>A PostgrXSQL function</lineannotation>
61666172
</programlisting>
61676173
</para>

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202407251
60+
#define CATALOG_VERSION_NO 202407252
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 47 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -3623,105 +3623,132 @@
36233623
prosrc => 'replace_text' },
36243624
{ oid => '2284', descr => 'replace text using regexp',
36253625
proname => 'regexp_replace', prorettype => 'text',
3626-
proargtypes => 'text text text', prosrc => 'textregexreplace_noopt' },
3626+
proargtypes => 'text text text',
3627+
proargnames => '{string, pattern, replacement}',
3628+
prosrc => 'textregexreplace_noopt' },
36273629
{ oid => '2285', descr => 'replace text using regexp',
36283630
proname => 'regexp_replace', prorettype => 'text',
3629-
proargtypes => 'text text text text', prosrc => 'textregexreplace' },
3631+
proargtypes => 'text text text text',
3632+
proargnames => '{string, pattern, replacement, flags}',
3633+
prosrc => 'textregexreplace' },
36303634
{ oid => '6251', descr => 'replace text using regexp',
36313635
proname => 'regexp_replace', prorettype => 'text',
36323636
proargtypes => 'text text text int4 int4 text',
3637+
proargnames => '{string, pattern, replacement, start, N, flags}',
36333638
prosrc => 'textregexreplace_extended' },
36343639
{ oid => '6252', descr => 'replace text using regexp',
36353640
proname => 'regexp_replace', prorettype => 'text',
36363641
proargtypes => 'text text text int4 int4',
3642+
proargnames => '{string, pattern, replacement, start, N}',
36373643
prosrc => 'textregexreplace_extended_no_flags' },
36383644
{ oid => '6253', descr => 'replace text using regexp',
36393645
proname => 'regexp_replace', prorettype => 'text',
36403646
proargtypes => 'text text text int4',
3647+
proargnames => '{string, pattern, replacement, start}',
36413648
prosrc => 'textregexreplace_extended_no_n' },
36423649
{ oid => '3396', descr => 'find first match for regexp',
36433650
proname => 'regexp_match', prorettype => '_text', proargtypes => 'text text',
3644-
prosrc => 'regexp_match_no_flags' },
3651+
proargnames => '{string, pattern}', prosrc => 'regexp_match_no_flags' },
36453652
{ oid => '3397', descr => 'find first match for regexp',
36463653
proname => 'regexp_match', prorettype => '_text',
3647-
proargtypes => 'text text text', prosrc => 'regexp_match' },
3654+
proargtypes => 'text text text', proargnames => '{string, pattern, flags}',
3655+
prosrc => 'regexp_match' },
36483656
{ oid => '2763', descr => 'find match(es) for regexp',
36493657
proname => 'regexp_matches', prorows => '1', proretset => 't',
36503658
prorettype => '_text', proargtypes => 'text text',
3651-
prosrc => 'regexp_matches_no_flags' },
3659+
proargnames => '{string, pattern}', prosrc => 'regexp_matches_no_flags' },
36523660
{ oid => '2764', descr => 'find match(es) for regexp',
36533661
proname => 'regexp_matches', prorows => '10', proretset => 't',
36543662
prorettype => '_text', proargtypes => 'text text text',
3655-
prosrc => 'regexp_matches' },
3663+
proargnames => '{string, pattern, flags}', prosrc => 'regexp_matches' },
36563664
{ oid => '6254', descr => 'count regexp matches',
36573665
proname => 'regexp_count', prorettype => 'int4', proargtypes => 'text text',
3658-
prosrc => 'regexp_count_no_start' },
3666+
proargnames => '{string, pattern}', prosrc => 'regexp_count_no_start' },
36593667
{ oid => '6255', descr => 'count regexp matches',
36603668
proname => 'regexp_count', prorettype => 'int4',
3661-
proargtypes => 'text text int4', prosrc => 'regexp_count_no_flags' },
3669+
proargtypes => 'text text int4', proargnames => '{string, pattern, start}',
3670+
prosrc => 'regexp_count_no_flags' },
36623671
{ oid => '6256', descr => 'count regexp matches',
36633672
proname => 'regexp_count', prorettype => 'int4',
3664-
proargtypes => 'text text int4 text', prosrc => 'regexp_count' },
3673+
proargtypes => 'text text int4 text',
3674+
proargnames => '{string, pattern, start, flags}', prosrc => 'regexp_count' },
36653675
{ oid => '6257', descr => 'position of regexp match',
36663676
proname => 'regexp_instr', prorettype => 'int4', proargtypes => 'text text',
3667-
prosrc => 'regexp_instr_no_start' },
3677+
proargnames => '{string, pattern}', prosrc => 'regexp_instr_no_start' },
36683678
{ oid => '6258', descr => 'position of regexp match',
36693679
proname => 'regexp_instr', prorettype => 'int4',
3670-
proargtypes => 'text text int4', prosrc => 'regexp_instr_no_n' },
3680+
proargtypes => 'text text int4', proargnames => '{string, pattern, start}',
3681+
prosrc => 'regexp_instr_no_n' },
36713682
{ oid => '6259', descr => 'position of regexp match',
36723683
proname => 'regexp_instr', prorettype => 'int4',
3673-
proargtypes => 'text text int4 int4', prosrc => 'regexp_instr_no_endoption' },
3684+
proargtypes => 'text text int4 int4',
3685+
proargnames => '{string, pattern, start, N}',
3686+
prosrc => 'regexp_instr_no_endoption' },
36743687
{ oid => '6260', descr => 'position of regexp match',
36753688
proname => 'regexp_instr', prorettype => 'int4',
36763689
proargtypes => 'text text int4 int4 int4',
3690+
proargnames => '{string, pattern, start, N, endoption}',
36773691
prosrc => 'regexp_instr_no_flags' },
36783692
{ oid => '6261', descr => 'position of regexp match',
36793693
proname => 'regexp_instr', prorettype => 'int4',
36803694
proargtypes => 'text text int4 int4 int4 text',
3695+
proargnames => '{string, pattern, start, N, endoption, flags}',
36813696
prosrc => 'regexp_instr_no_subexpr' },
36823697
{ oid => '6262', descr => 'position of regexp match',
36833698
proname => 'regexp_instr', prorettype => 'int4',
36843699
proargtypes => 'text text int4 int4 int4 text int4',
3700+
proargnames => '{string, pattern, start, N, endoption, flags, subexpr}',
36853701
prosrc => 'regexp_instr' },
36863702
{ oid => '6263', descr => 'test for regexp match',
36873703
proname => 'regexp_like', prorettype => 'bool', proargtypes => 'text text',
3688-
prosrc => 'regexp_like_no_flags' },
3704+
proargnames => '{string, pattern}', prosrc => 'regexp_like_no_flags' },
36893705
{ oid => '6264', descr => 'test for regexp match',
36903706
proname => 'regexp_like', prorettype => 'bool',
3691-
proargtypes => 'text text text', prosrc => 'regexp_like' },
3707+
proargtypes => 'text text text', proargnames => '{string, pattern,flags}',
3708+
prosrc => 'regexp_like' },
36923709
{ oid => '6265', descr => 'extract substring that matches regexp',
36933710
proname => 'regexp_substr', prorettype => 'text', proargtypes => 'text text',
3694-
prosrc => 'regexp_substr_no_start' },
3711+
proargnames => '{string, pattern}', prosrc => 'regexp_substr_no_start' },
36953712
{ oid => '6266', descr => 'extract substring that matches regexp',
36963713
proname => 'regexp_substr', prorettype => 'text',
3697-
proargtypes => 'text text int4', prosrc => 'regexp_substr_no_n' },
3714+
proargtypes => 'text text int4', proargnames => '{string, pattern, start}',
3715+
prosrc => 'regexp_substr_no_n' },
36983716
{ oid => '6267', descr => 'extract substring that matches regexp',
36993717
proname => 'regexp_substr', prorettype => 'text',
3700-
proargtypes => 'text text int4 int4', prosrc => 'regexp_substr_no_flags' },
3718+
proargtypes => 'text text int4 int4',
3719+
proargnames => '{string, pattern, start, N}',
3720+
prosrc => 'regexp_substr_no_flags' },
37013721
{ oid => '6268', descr => 'extract substring that matches regexp',
37023722
proname => 'regexp_substr', prorettype => 'text',
37033723
proargtypes => 'text text int4 int4 text',
3724+
proargnames => '{string, pattern, start, N, flags}',
37043725
prosrc => 'regexp_substr_no_subexpr' },
37053726
{ oid => '6269', descr => 'extract substring that matches regexp',
37063727
proname => 'regexp_substr', prorettype => 'text',
3707-
proargtypes => 'text text int4 int4 text int4', prosrc => 'regexp_substr' },
3728+
proargtypes => 'text text int4 int4 text int4',
3729+
proargnames => '{string, pattern, start, N, flags, subexpr}',
3730+
prosrc => 'regexp_substr' },
37083731
{ oid => '2088', descr => 'split string by field_sep and return field_num',
37093732
proname => 'split_part', prorettype => 'text',
37103733
proargtypes => 'text text int4', prosrc => 'split_part' },
37113734
{ oid => '2765', descr => 'split string by pattern',
37123735
proname => 'regexp_split_to_table', prorows => '1000', proretset => 't',
37133736
prorettype => 'text', proargtypes => 'text text',
3737+
proargnames => '{string, pattern}',
37143738
prosrc => 'regexp_split_to_table_no_flags' },
37153739
{ oid => '2766', descr => 'split string by pattern',
37163740
proname => 'regexp_split_to_table', prorows => '1000', proretset => 't',
37173741
prorettype => 'text', proargtypes => 'text text text',
3742+
proargnames => '{string, pattern, flags}',
37183743
prosrc => 'regexp_split_to_table' },
37193744
{ oid => '2767', descr => 'split string by pattern',
37203745
proname => 'regexp_split_to_array', prorettype => '_text',
3721-
proargtypes => 'text text', prosrc => 'regexp_split_to_array_no_flags' },
3746+
proargtypes => 'text text', proargnames => '{string, pattern}',
3747+
prosrc => 'regexp_split_to_array_no_flags' },
37223748
{ oid => '2768', descr => 'split string by pattern',
37233749
proname => 'regexp_split_to_array', prorettype => '_text',
3724-
proargtypes => 'text text text', prosrc => 'regexp_split_to_array' },
3750+
proargtypes => 'text text text', proargnames => '{string, pattern, flags}',
3751+
prosrc => 'regexp_split_to_array' },
37253752
{ oid => '6330', descr => 'convert int4 number to binary',
37263753
proname => 'to_bin', prorettype => 'text', proargtypes => 'int4',
37273754
prosrc => 'to_bin32' },

0 commit comments

Comments
 (0)