From 49b27ab55149ade3b00de782be7ef53b4f9722c9 Mon Sep 17 00:00:00 2001 From: Itagaki Takahiro Date: Tue, 24 Aug 2010 06:30:44 +0000 Subject: [PATCH] Add string functions: concat(), concat_ws(), left(), right(), and reverse(). Pavel Stehule, reviewed by me. --- doc/src/sgml/func.sgml | 85 ++++++++++++++++- src/backend/utils/adt/varlena.c | 148 ++++++++++++++++++++++++++++- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 12 ++- src/include/utils/builtins.h | 8 +- src/test/regress/expected/text.out | 67 +++++++++++++ src/test/regress/sql/text.sql | 13 +++ 7 files changed, 331 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ae5139cb9e..66f72f3214 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -1250,6 +1250,12 @@ chr + + concat + + + concat_ws + convert @@ -1268,6 +1274,9 @@ initcap + + left + lpad @@ -1295,6 +1304,12 @@ replace + + reverse + + + right + rpad @@ -1374,6 +1389,34 @@ A + + + concat(str "any" + [, str "any" [, ...] ]) + + text + + Concatenate all arguments. NULL arguments are ignored. + + concat('abcde', 2, NULL, 22) + abcde222 + + + + + concat_ws(sep text, + str "any" + [, str "any" [, ...] ]) + + text + + Concatenate all but first arguments with separators. The first + parameter is used as a separator. NULL arguments are ignored. + + concat_ws(',', 'abcde', 2, NULL, 22) + abcde,2,22 + + convert(string bytea, @@ -1465,6 +1508,20 @@ Hi Thomas + + + left(str text, + n int) + + text + + Return first n characters in the string. When n + is negative, return all but last |n| characters. + + left('abcde', 2) + ab + + length(string) int @@ -1678,6 +1735,32 @@ abXXefabXXef + + + reverse(str) + + text + + Return reversed string. + + reverse('abcde') + edcba + + + + + right(str text, + n int) + + text + + Return last n characters in the string. When n + is negative, return all but first |n| characters. + + right('abcde', 2) + de + + rpad(string text, diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index 1ad4667d63..cf62dd1b57 100644 --- a/src/backend/utils/adt/varlena.c +++ b/src/backend/utils/adt/varlena.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.179 2010/08/10 21:51:00 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.180 2010/08/24 06:30:43 itagaki Exp $ * *------------------------------------------------------------------------- */ @@ -3556,3 +3556,149 @@ string_agg_finalfn(PG_FUNCTION_ARGS) else PG_RETURN_NULL(); } + +static text * +concat_internal(const char *sepstr, int seplen, int argidx, FunctionCallInfo fcinfo) +{ + StringInfoData str; + text *result; + int i; + + initStringInfo(&str); + + for (i = argidx; i < PG_NARGS(); i++) + { + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + Oid typOutput; + bool typIsVarlena; + + if (i > argidx) + appendBinaryStringInfo(&str, sepstr, seplen); + + /* append n-th value */ + value = PG_GETARG_DATUM(i); + valtype = get_fn_expr_argtype(fcinfo->flinfo, i); + getTypeOutputInfo(valtype, &typOutput, &typIsVarlena); + appendStringInfoString(&str, + OidOutputFunctionCall(typOutput, value)); + } + } + + result = cstring_to_text_with_len(str.data, str.len); + pfree(str.data); + + return result; +} + +/* + * Concatenate all arguments. NULL arguments are ignored. + */ +Datum +text_concat(PG_FUNCTION_ARGS) +{ + PG_RETURN_TEXT_P(concat_internal(NULL, 0, 0, fcinfo)); +} + +/* + * Concatenate all but first argument values with separators. The first + * parameter is used as a separator. NULL arguments are ignored. + */ +Datum +text_concat_ws(PG_FUNCTION_ARGS) +{ + text *sep; + + /* return NULL when separator is NULL */ + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + sep = PG_GETARG_TEXT_PP(0); + + PG_RETURN_TEXT_P(concat_internal( + VARDATA_ANY(sep), VARSIZE_ANY_EXHDR(sep), 1, fcinfo)); +} + +/* + * Return first n characters in the string. When n is negative, + * return all but last |n| characters. + */ +Datum +text_left(PG_FUNCTION_ARGS) +{ + text *str = PG_GETARG_TEXT_PP(0); + const char *p = VARDATA_ANY(str); + int len = VARSIZE_ANY_EXHDR(str); + int n = PG_GETARG_INT32(1); + int rlen; + + if (n < 0) + n = pg_mbstrlen_with_len(p, len) + n; + rlen = pg_mbcharcliplen(p, len, n); + + PG_RETURN_TEXT_P(cstring_to_text_with_len(p, rlen)); +} + +/* + * Return last n characters in the string. When n is negative, + * return all but first |n| characters. + */ +Datum +text_right(PG_FUNCTION_ARGS) +{ + text *str = PG_GETARG_TEXT_PP(0); + const char *p = VARDATA_ANY(str); + int len = VARSIZE_ANY_EXHDR(str); + int n = PG_GETARG_INT32(1); + int off; + + if (n < 0) + n = -n; + else + n = pg_mbstrlen_with_len(p, len) - n; + off = pg_mbcharcliplen(p, len, n); + + PG_RETURN_TEXT_P(cstring_to_text_with_len(p + off, len - off)); +} + +/* + * Return reversed string + */ +Datum +text_reverse(PG_FUNCTION_ARGS) +{ + text *str = PG_GETARG_TEXT_PP(0); + const char *p = VARDATA_ANY(str); + int len = VARSIZE_ANY_EXHDR(str); + const char *endp = p + len; + text *result; + char *dst; + + result = palloc(len + VARHDRSZ); + dst = (char*) VARDATA(result) + len; + SET_VARSIZE(result, len + VARHDRSZ); + + if (pg_database_encoding_max_length() > 1) + { + /* multibyte version */ + while (p < endp) + { + int sz; + + sz = pg_mblen(p); + dst -= sz; + memcpy(dst, p, sz); + p += sz; + } + } + else + { + /* single byte version */ + while (p < endp) + *(--dst) = *p++; + } + + PG_RETURN_TEXT_P(result); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 5c538e6f79..f5a96c81f5 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.596 2010/08/21 10:59:17 mha Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.597 2010/08/24 06:30:43 itagaki Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201008211 +#define CATALOG_VERSION_NO 201008241 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index c4dd14a71e..b3f1e14fd3 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.580 2010/08/21 10:59:17 mha Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.581 2010/08/24 06:30:43 itagaki Exp $ * * NOTES * The script catalog/genbki.pl reads this file and generates .bki @@ -2722,6 +2722,16 @@ DESCR("I/O"); 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_ )); DESCR("I/O"); +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_ )); +DESCR("concatenate values"); +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_ )); +DESCR("concatenate values with separators"); +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_ )); +DESCR("return the first n characters"); +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_ )); +DESCR("return the last n characters"); +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_ )); +DESCR("reverse text"); 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_ )); DESCR("length in bits"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 258a374943..4612cb3543 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.354 2010/08/10 21:51:00 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.355 2010/08/24 06:30:44 itagaki Exp $ * *------------------------------------------------------------------------- */ @@ -733,6 +733,12 @@ extern Datum pg_column_size(PG_FUNCTION_ARGS); extern Datum string_agg_transfn(PG_FUNCTION_ARGS); extern Datum string_agg_finalfn(PG_FUNCTION_ARGS); +extern Datum text_concat(PG_FUNCTION_ARGS); +extern Datum text_concat_ws(PG_FUNCTION_ARGS); +extern Datum text_left(PG_FUNCTION_ARGS); +extern Datum text_right(PG_FUNCTION_ARGS); +extern Datum text_reverse(PG_FUNCTION_ARGS); + /* version.c */ extern Datum pgsql_version(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/text.out b/src/test/regress/expected/text.out index 08d002fe71..84f4a5cda8 100644 --- a/src/test/regress/expected/text.out +++ b/src/test/regress/expected/text.out @@ -51,3 +51,70 @@ ERROR: operator does not exist: integer || numeric LINE 1: select 3 || 4.0; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. +/* + * string functions + */ +select concat('one'); + concat +-------- + one +(1 row) + +select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); + concat +---------------------- + 123hellotf03-09-2010 +(1 row) + +select concat_ws('#','one'); + concat_ws +----------- + one +(1 row) + +select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); + concat_ws +---------------------------- + 1#2#3#hello#t#f#03-09-2010 +(1 row) + +select concat_ws(',',10,20,null,30); + concat_ws +----------- + 10,20,30 +(1 row) + +select concat_ws('',10,20,null,30); + concat_ws +----------- + 102030 +(1 row) + +select concat_ws(NULL,10,20,null,30) is null; + ?column? +---------- + t +(1 row) + +select reverse('abcde'); + reverse +--------- + edcba +(1 row) + +select i, left('ahoj', i), right('ahoj', i) from generate_series(-5, 5) t(i) order by i; + i | left | right +----+------+------- + -5 | | + -4 | | + -3 | a | j + -2 | ah | oj + -1 | aho | hoj + 0 | | + 1 | a | j + 2 | ah | oj + 3 | aho | hoj + 4 | ahoj | ahoj + 5 | ahoj | ahoj +(11 rows) + diff --git a/src/test/regress/sql/text.sql b/src/test/regress/sql/text.sql index b739e56e2d..a8768ee81a 100644 --- a/src/test/regress/sql/text.sql +++ b/src/test/regress/sql/text.sql @@ -28,3 +28,16 @@ select 'four: ' || 2+2; -- but not this: select 3 || 4.0; + +/* + * string functions + */ +select concat('one'); +select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); +select concat_ws('#','one'); +select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); +select concat_ws(',',10,20,null,30); +select concat_ws('',10,20,null,30); +select concat_ws(NULL,10,20,null,30) is null; +select reverse('abcde'); +select i, left('ahoj', i), right('ahoj', i) from generate_series(-5, 5) t(i) order by i; -- 2.40.0