Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit580f872

Browse files
committed
Add argument names to the regexp_XXX functions.
This change allows these functions to be called using named-argumentnotation, which can be helpful for readability, particularly forthe 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 ourdocumentation table 9.10.The citext extension provides citext-aware versions of some ofthese 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 argumentsare allowed.Jian He, reviewed by Dian Fay and othersDiscussion:https://postgr.es/m/CACJufxG3NFKKsh6x4fRLv8h3V-HvN4W5dA=zNKMxsNcDwOKang@mail.gmail.com
1 parent05faf06 commit580f872

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 REPLACEFUNCTIONregexp_match(string citext, pattern citext) RETURNSTEXT[]AS $$
8+
SELECTpg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text,'i' );
9+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
10+
11+
CREATE OR REPLACEFUNCTIONregexp_match(string citext, pattern citext, flagstext) RETURNSTEXT[]AS $$
12+
SELECTpg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHENpg_catalog.strpos($3,'c')=0 THEN $3||'i' ELSE $3 END );
13+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
14+
15+
CREATE OR REPLACEFUNCTIONregexp_matches(string citext, pattern citext) RETURNS SETOFTEXT[]AS $$
16+
SELECTpg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text,'i' );
17+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS1;
18+
19+
CREATE OR REPLACEFUNCTIONregexp_matches(string citext, pattern citext, flagstext) RETURNS SETOFTEXT[]AS $$
20+
SELECTpg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHENpg_catalog.strpos($3,'c')=0 THEN $3||'i' ELSE $3 END );
21+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS10;
22+
23+
CREATE OR REPLACEFUNCTIONregexp_replace(string citext, pattern citext, replacementtext) returnsTEXTAS $$
24+
SELECTpg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3,'i');
25+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
26+
27+
CREATE OR REPLACEFUNCTIONregexp_replace(string citext, pattern citext, replacementtext, flagstext) returnsTEXTAS $$
28+
SELECTpg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHENpg_catalog.strpos($4,'c')=0 THEN $4||'i' ELSE $4 END);
29+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
30+
31+
CREATE OR REPLACEFUNCTIONregexp_split_to_array(string citext, pattern citext) RETURNSTEXT[]AS $$
32+
SELECTpg_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 REPLACEFUNCTIONregexp_split_to_array(string citext, pattern citext, flagstext) RETURNSTEXT[]AS $$
36+
SELECTpg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHENpg_catalog.strpos($3,'c')=0 THEN $3||'i' ELSE $3 END );
37+
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
38+
39+
CREATE OR REPLACEFUNCTIONregexp_split_to_table(string citext, pattern citext) RETURNS SETOFTEXTAS $$
40+
SELECTpg_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 REPLACEFUNCTIONregexp_split_to_table(string citext, pattern citext, flagstext) RETURNS SETOFTEXTAS $$
44+
SELECTpg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHENpg_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+
Thesource<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-
#defineCATALOG_VERSION_NO202407251
60+
#defineCATALOG_VERSION_NO202407252
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)
[8]ページ先頭
©2009-2025 Movatter.jp