Skip to content

Commit 49b27ab

Browse files
ItGackyItGacky
committed
Add string functions: concat(), concat_ws(), left(), right(), and reverse().
Pavel Stehule, reviewed by me.
1 parent b9defe0 commit 49b27ab

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+
static text *
3561+
concat_internal(const char *sepstr, int seplen, int argidx, FunctionCallInfo fcinfo)
3562+
{
3563+
StringInfoData str;
3564+
text *result;
3565+
int i;
3566+
3567+
initStringInfo(&str);
3568+
3569+
for (i = argidx; i < PG_NARGS(); i++)
3570+
{
3571+
if (!PG_ARGISNULL(i))
3572+
{
3573+
Oid valtype;
3574+
Datum value;
3575+
Oid typOutput;
3576+
bool typIsVarlena;
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+
return result;
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+
const char *p = VARDATA_ANY(str);
3633+
int len = VARSIZE_ANY_EXHDR(str);
3634+
int n = PG_GETARG_INT32(1);
3635+
int rlen;
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+
const char *p = VARDATA_ANY(str);
3653+
int len = VARSIZE_ANY_EXHDR(str);
3654+
int n = PG_GETARG_INT32(1);
3655+
int off;
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+
const char *p = VARDATA_ANY(str);
3674+
int len = VARSIZE_ANY_EXHDR(str);
3675+
const char *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+
int sz;
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-
#define CATALOG_VERSION_NO 201008211
56+
#define CATALOG_VERSION_NO 201008241
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(insert OID = 1799 ( oidout PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2275 "26" _null_ _null_ _null_ _null_ oidout _null_ _null_ _null_ ));
27232723
DESCR("I/O");
27242724

2725+
DATA(insert OID = 3058 ( concat PGNSP PGUID 12 1 0 2276 f f f f f s 1 0 25 "2276" "{2276}" "{v}" _null_ _null_ text_concat _null_ _null_ _null_ ));
2726+
DESCR("concatenate values");
2727+
DATA(insert OID = 3059 ( concat_ws PGNSP PGUID 12 1 0 2276 f f f f f s 2 0 25 "25 2276" "{25,2276}" "{i,v}" _null_ _null_ text_concat_ws _null_ _null_ _null_ ));
2728+
DESCR("concatenate values with separators");
2729+
DATA(insert OID = 3060 ( left PGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "25 23" _null_ _null_ _null_ _null_ text_left _null_ _null_ _null_ ));
2730+
DESCR("return the first n characters");
2731+
DATA(insert OID = 3061 ( right PGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "25 23" _null_ _null_ _null_ _null_ text_right _null_ _null_ _null_ ));
2732+
DESCR("return the last n characters");
2733+
DATA(insert OID = 3062 ( reverse PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "25" _null_ _null_ _null_ _null_ text_reverse _null_ _null_ _null_ ));
2734+
DESCR("reverse text");
27252735

27262736
DATA(insert OID = 1810 ( bit_length PGNSP PGUID 14 1 0 0 f f f t f i 1 0 23 "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
extern Datum string_agg_transfn(PG_FUNCTION_ARGS);
734734
extern Datum string_agg_finalfn(PG_FUNCTION_ARGS);
735735

736+
extern Datum text_concat(PG_FUNCTION_ARGS);
737+
extern Datum text_concat_ws(PG_FUNCTION_ARGS);
738+
extern Datum text_left(PG_FUNCTION_ARGS);
739+
extern Datum text_right(PG_FUNCTION_ARGS);
740+
extern Datum text_reverse(PG_FUNCTION_ARGS);
741+
736742
/* version.c */
737743
extern Datum pgsql_version(PG_FUNCTION_ARGS);
738744

0 commit comments

Comments
 (0)