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

Commit49b27ab

Browse files
committed
Add string functions: concat(), concat_ws(), left(), right(), and reverse().
Pavel Stehule, reviewed by me.
1 parentb9defe0 commit49b27ab

File tree

7 files changed

+331
-6
lines changed

7 files changed

+331
-6
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 84 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.529 2010/08/17 04:37:20 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.530 2010/08/24 06:30:43 itagaki Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -1250,6 +1250,12 @@
12501250
<indexterm>
12511251
<primary>chr</primary>
12521252
</indexterm>
1253+
<indexterm>
1254+
<primary>concat</primary>
1255+
</indexterm>
1256+
<indexterm>
1257+
<primary>concat_ws</primary>
1258+
</indexterm>
12531259
<indexterm>
12541260
<primary>convert</primary>
12551261
</indexterm>
@@ -1268,6 +1274,9 @@
12681274
<indexterm>
12691275
<primary>initcap</primary>
12701276
</indexterm>
1277+
<indexterm>
1278+
<primary>left</primary>
1279+
</indexterm>
12711280
<indexterm>
12721281
<primary>lpad</primary>
12731282
</indexterm>
@@ -1295,6 +1304,12 @@
12951304
<indexterm>
12961305
<primary>replace</primary>
12971306
</indexterm>
1307+
<indexterm>
1308+
<primary>reverse</primary>
1309+
</indexterm>
1310+
<indexterm>
1311+
<primary>right</primary>
1312+
</indexterm>
12981313
<indexterm>
12991314
<primary>rpad</primary>
13001315
</indexterm>
@@ -1374,6 +1389,34 @@
13741389
<entry><literal>A</literal></entry>
13751390
</row>
13761391

1392+
<row>
1393+
<entry>
1394+
<literal><function>concat</function>(<parameter>str</parameter> <type>"any"</type>
1395+
[, <parameter>str</parameter> <type>"any"</type> [, ...] ])</literal>
1396+
</entry>
1397+
<entry><type>text</type></entry>
1398+
<entry>
1399+
Concatenate all arguments. NULL arguments are ignored.
1400+
</entry>
1401+
<entry><literal>concat('abcde', 2, NULL, 22)</literal></entry>
1402+
<entry><literal>abcde222</literal></entry>
1403+
</row>
1404+
1405+
<row>
1406+
<entry>
1407+
<literal><function>concat_ws</function>(<parameter>sep</parameter> <type>text</type>,
1408+
<parameter>str</parameter> <type>"any"</type>
1409+
[, <parameter>str</parameter> <type>"any"</type> [, ...] ])</literal>
1410+
</entry>
1411+
<entry><type>text</type></entry>
1412+
<entry>
1413+
Concatenate all but first arguments with separators. The first
1414+
parameter is used as a separator. NULL arguments are ignored.
1415+
</entry>
1416+
<entry><literal>concat_ws(',', 'abcde', 2, NULL, 22)</literal></entry>
1417+
<entry><literal>abcde,2,22</literal></entry>
1418+
</row>
1419+
13771420
<row>
13781421
<entry>
13791422
<literal><function>convert</function>(<parameter>string</parameter> <type>bytea</type>,
@@ -1465,6 +1508,20 @@
14651508
<entry><literal>Hi Thomas</literal></entry>
14661509
</row>
14671510

1511+
<row>
1512+
<entry>
1513+
<literal><function>left</function>(<parameter>str</parameter> <type>text</type>,
1514+
<parameter>n</parameter> <type>int</type>)</literal>
1515+
</entry>
1516+
<entry><type>text</type></entry>
1517+
<entry>
1518+
Return first <replaceable>n</> characters in the string. When <replaceable>n</>
1519+
is negative, return all but last |<replaceable>n</>| characters.
1520+
</entry>
1521+
<entry><literal>left('abcde', 2)</literal></entry>
1522+
<entry><literal>ab</literal></entry>
1523+
</row>
1524+
14681525
<row>
14691526
<entry><literal><function>length</function>(<parameter>string</parameter>)</literal></entry>
14701527
<entry><type>int</type></entry>
@@ -1678,6 +1735,32 @@
16781735
<entry><literal>abXXefabXXef</literal></entry>
16791736
</row>
16801737

1738+
<row>
1739+
<entry>
1740+
<literal><function>reverse</function>(<parameter>str</parameter>)</literal>
1741+
</entry>
1742+
<entry><type>text</type></entry>
1743+
<entry>
1744+
Return reversed string.
1745+
</entry>
1746+
<entry><literal>reverse('abcde')</literal></entry>
1747+
<entry><literal>edcba</literal></entry>
1748+
</row>
1749+
1750+
<row>
1751+
<entry>
1752+
<literal><function>right</function>(<parameter>str</parameter> <type>text</type>,
1753+
<parameter>n</parameter> <type>int</type>)</literal>
1754+
</entry>
1755+
<entry><type>text</type></entry>
1756+
<entry>
1757+
Return last <replaceable>n</> characters in the string. When <replaceable>n</>
1758+
is negative, return all but first |<replaceable>n</>| characters.
1759+
</entry>
1760+
<entry><literal>right('abcde', 2)</literal></entry>
1761+
<entry><literal>de</literal></entry>
1762+
</row>
1763+
16811764
<row>
16821765
<entry>
16831766
<literal><function>rpad</function>(<parameter>string</parameter> <type>text</type>,

‎src/backend/utils/adt/varlena.c

Lines changed: 147 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.179 2010/08/10 21:51:00 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.180 2010/08/24 06:30:43 itagaki Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3556,3 +3556,149 @@ string_agg_finalfn(PG_FUNCTION_ARGS)
35563556
else
35573557
PG_RETURN_NULL();
35583558
}
3559+
3560+
statictext*
3561+
concat_internal(constchar*sepstr,intseplen,intargidx,FunctionCallInfofcinfo)
3562+
{
3563+
StringInfoDatastr;
3564+
text*result;
3565+
inti;
3566+
3567+
initStringInfo(&str);
3568+
3569+
for (i=argidx;i<PG_NARGS();i++)
3570+
{
3571+
if (!PG_ARGISNULL(i))
3572+
{
3573+
Oidvaltype;
3574+
Datumvalue;
3575+
OidtypOutput;
3576+
booltypIsVarlena;
3577+
3578+
if (i>argidx)
3579+
appendBinaryStringInfo(&str,sepstr,seplen);
3580+
3581+
/* append n-th value */
3582+
value=PG_GETARG_DATUM(i);
3583+
valtype=get_fn_expr_argtype(fcinfo->flinfo,i);
3584+
getTypeOutputInfo(valtype,&typOutput,&typIsVarlena);
3585+
appendStringInfoString(&str,
3586+
OidOutputFunctionCall(typOutput,value));
3587+
}
3588+
}
3589+
3590+
result=cstring_to_text_with_len(str.data,str.len);
3591+
pfree(str.data);
3592+
3593+
returnresult;
3594+
}
3595+
3596+
/*
3597+
* Concatenate all arguments. NULL arguments are ignored.
3598+
*/
3599+
Datum
3600+
text_concat(PG_FUNCTION_ARGS)
3601+
{
3602+
PG_RETURN_TEXT_P(concat_internal(NULL,0,0,fcinfo));
3603+
}
3604+
3605+
/*
3606+
* Concatenate all but first argument values with separators. The first
3607+
* parameter is used as a separator. NULL arguments are ignored.
3608+
*/
3609+
Datum
3610+
text_concat_ws(PG_FUNCTION_ARGS)
3611+
{
3612+
text*sep;
3613+
3614+
/* return NULL when separator is NULL */
3615+
if (PG_ARGISNULL(0))
3616+
PG_RETURN_NULL();
3617+
3618+
sep=PG_GETARG_TEXT_PP(0);
3619+
3620+
PG_RETURN_TEXT_P(concat_internal(
3621+
VARDATA_ANY(sep),VARSIZE_ANY_EXHDR(sep),1,fcinfo));
3622+
}
3623+
3624+
/*
3625+
* Return first n characters in the string. When n is negative,
3626+
* return all but last |n| characters.
3627+
*/
3628+
Datum
3629+
text_left(PG_FUNCTION_ARGS)
3630+
{
3631+
text*str=PG_GETARG_TEXT_PP(0);
3632+
constchar*p=VARDATA_ANY(str);
3633+
intlen=VARSIZE_ANY_EXHDR(str);
3634+
intn=PG_GETARG_INT32(1);
3635+
intrlen;
3636+
3637+
if (n<0)
3638+
n=pg_mbstrlen_with_len(p,len)+n;
3639+
rlen=pg_mbcharcliplen(p,len,n);
3640+
3641+
PG_RETURN_TEXT_P(cstring_to_text_with_len(p,rlen));
3642+
}
3643+
3644+
/*
3645+
* Return last n characters in the string. When n is negative,
3646+
* return all but first |n| characters.
3647+
*/
3648+
Datum
3649+
text_right(PG_FUNCTION_ARGS)
3650+
{
3651+
text*str=PG_GETARG_TEXT_PP(0);
3652+
constchar*p=VARDATA_ANY(str);
3653+
intlen=VARSIZE_ANY_EXHDR(str);
3654+
intn=PG_GETARG_INT32(1);
3655+
intoff;
3656+
3657+
if (n<0)
3658+
n=-n;
3659+
else
3660+
n=pg_mbstrlen_with_len(p,len)-n;
3661+
off=pg_mbcharcliplen(p,len,n);
3662+
3663+
PG_RETURN_TEXT_P(cstring_to_text_with_len(p+off,len-off));
3664+
}
3665+
3666+
/*
3667+
* Return reversed string
3668+
*/
3669+
Datum
3670+
text_reverse(PG_FUNCTION_ARGS)
3671+
{
3672+
text*str=PG_GETARG_TEXT_PP(0);
3673+
constchar*p=VARDATA_ANY(str);
3674+
intlen=VARSIZE_ANY_EXHDR(str);
3675+
constchar*endp=p+len;
3676+
text*result;
3677+
char*dst;
3678+
3679+
result=palloc(len+VARHDRSZ);
3680+
dst= (char*)VARDATA(result)+len;
3681+
SET_VARSIZE(result,len+VARHDRSZ);
3682+
3683+
if (pg_database_encoding_max_length()>1)
3684+
{
3685+
/* multibyte version */
3686+
while (p<endp)
3687+
{
3688+
intsz;
3689+
3690+
sz=pg_mblen(p);
3691+
dst-=sz;
3692+
memcpy(dst,p,sz);
3693+
p+=sz;
3694+
}
3695+
}
3696+
else
3697+
{
3698+
/* single byte version */
3699+
while (p<endp)
3700+
*(--dst)=*p++;
3701+
}
3702+
3703+
PG_RETURN_TEXT_P(result);
3704+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.596 2010/08/21 10:59:17 mha Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.597 2010/08/24 06:30:43 itagaki Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201008211
56+
#defineCATALOG_VERSION_NO201008241
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.580 2010/08/21 10:59:17 mha Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.581 2010/08/24 06:30:43 itagaki Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.pl reads this file and generates .bki
@@ -2722,6 +2722,16 @@ DESCR("I/O");
27222722
DATA(insertOID=1799 (oidoutPGNSPPGUID12100ffftfi102275"26"_null__null__null__null_oidout_null__null__null_ ));
27232723
DESCR("I/O");
27242724

2725+
DATA(insertOID=3058 (concatPGNSPPGUID12102276fffffs1025"2276""{2276}""{v}"_null__null_text_concat_null__null__null_ ));
2726+
DESCR("concatenate values");
2727+
DATA(insertOID=3059 (concat_wsPGNSPPGUID12102276fffffs2025"25 2276""{25,2276}""{i,v}"_null__null_text_concat_ws_null__null__null_ ));
2728+
DESCR("concatenate values with separators");
2729+
DATA(insertOID=3060 (leftPGNSPPGUID12100ffftfi2025"25 23"_null__null__null__null_text_left_null__null__null_ ));
2730+
DESCR("return the first n characters");
2731+
DATA(insertOID=3061 (rightPGNSPPGUID12100ffftfi2025"25 23"_null__null__null__null_text_right_null__null__null_ ));
2732+
DESCR("return the last n characters");
2733+
DATA(insertOID=3062 (reversePGNSPPGUID12100ffftfi1025"25"_null__null__null__null_text_reverse_null__null__null_ ));
2734+
DESCR("reverse text");
27252735

27262736
DATA(insertOID=1810 (bit_lengthPGNSPPGUID14100ffftfi1023"17"_null__null__null__null_"select pg_catalog.octet_length($1) * 8"_null__null__null_ ));
27272737
DESCR("length in bits");

‎src/include/utils/builtins.h

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.354 2010/08/10 21:51:00 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.355 2010/08/24 06:30:44 itagaki Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -733,6 +733,12 @@ extern Datum pg_column_size(PG_FUNCTION_ARGS);
733733
externDatumstring_agg_transfn(PG_FUNCTION_ARGS);
734734
externDatumstring_agg_finalfn(PG_FUNCTION_ARGS);
735735

736+
externDatumtext_concat(PG_FUNCTION_ARGS);
737+
externDatumtext_concat_ws(PG_FUNCTION_ARGS);
738+
externDatumtext_left(PG_FUNCTION_ARGS);
739+
externDatumtext_right(PG_FUNCTION_ARGS);
740+
externDatumtext_reverse(PG_FUNCTION_ARGS);
741+
736742
/* version.c */
737743
externDatumpgsql_version(PG_FUNCTION_ARGS);
738744

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp