diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 70dab53..a25307f 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 1251,1256 ****
--- 1251,1262 ----
chr
+ concat
+
+
+ concat_ws
+
+
convert
***************
*** 1266,1274 ****
--- 1272,1286 ----
encode
+ format
+
+
initcap
+ left
+
+
lpad
***************
*** 1296,1301 ****
--- 1308,1319 ----
replace
+ reverse
+
+
+ right
+
+
rpad
***************
*** 1376,1381 ****
--- 1394,1427 ----
+ concat(str "any",
+ [, str "any" [...] ])
+
+ text
+
+ Concatenate all argument values. NULL arguments are ignored.
+
+ concat('abcde', 2, NULL, 22)
+ abcde222
+
+
+
+
+ concat_ws(sep text,
+ str "any",
+ [, str "any" [...] ])
+
+ text
+
+ Concatenate all but first argument values. The first parameter is used
+ as a separator. NULL arguments are ignored.
+
+ concat_ws(',', 'abcde', 2, NULL, 22)
+ abcde,2,22
+
+
+
+
convert(string bytea,
src_encoding name,
dest_encoding name)
***************
*** 1454,1459 ****
--- 1500,1522 ----
+
+ format(formatstr text
+ [, value any [...] ])
+
+ text
+
+ Format argument values into a string like sprintf function in C library.
+ In addition to standard format characters, it supports %iq
+ (quoted identifier) and %lq (quoted literal).
+ Each format specifiler can also have length modifiler.
+ Write %% to emit a literal %.
+
+ format('%d %s xxx: %s', 10, 'foo', current_date)
+ 10 foo xxx: 2010-03-05
+
+
+
initcap(string)
text
***************
*** 1466,1471 ****
--- 1529,1548 ----
+
+ left(str text,
+ n int )
+
+ text
+
+ Returns first n chars of string. When n is negative, then returns chars from begin
+ to n char from right.
+
+ left('abcde', 2)
+ ab
+
+
+
length(string)
int
***************
*** 1680,1685 ****
--- 1757,1788 ----
+ reverse(str)
+
+ text
+
+ Returns reversed string.
+
+ reverse('abcde')
+ edcba
+
+
+
+
+ right(str text,
+ n int )
+
+ text
+
+ Returns last n chars of string. When n is negative, then returns chars from n char
+ to end of string
+
+ right('abcde', 2)
+ de
+
+
+
+
rpad(string text,
length int
, fill text)
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index af28c15..c684053 100644
*** a/src/backend/utils/adt/varlena.c
--- b/src/backend/utils/adt/varlena.c
***************
*** 21,26 ****
--- 21,27 ----
#include "libpq/md5.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+ #include "parser/parse_coerce.h"
#include "parser/scansup.h"
#include "regex/regex.h"
#include "utils/builtins.h"
*************** string_agg_finalfn(PG_FUNCTION_ARGS)
*** 3415,3417 ****
--- 3416,4155 ----
else
PG_RETURN_NULL();
}
+
+ static text *
+ concat_internal(const char *sepstr, int seplen, FunctionCallInfo fcinfo)
+ {
+ StringInfoData str;
+ text *result;
+ int i;
+
+ initStringInfo(&str);
+
+ for (i = (sepstr == NULL ? 0 : 1); i < PG_NARGS(); i++)
+ {
+ if (!PG_ARGISNULL(i))
+ {
+ Oid valtype;
+ Datum value;
+ Oid typOutput;
+ bool typIsVarlena;
+
+ if (i > 1)
+ 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;
+ }
+
+ /*
+ * Concatenates arguments. NULL arguments are skipped.
+ */
+ Datum
+ text_concat(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_TEXT_P(concat_internal(NULL, 0, fcinfo));
+ }
+
+ /*
+ * Concatenates arguments. Each argument is separated with the first argument.
+ * NULL arguments are skipped.
+ */
+ 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), fcinfo));
+ }
+
+ /*
+ * Returns first n characters. When n is negative, returns chars without
+ * 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));
+ }
+
+ /*
+ * Returns last n characters. When n is negative, returns string without
+ * 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));
+ }
+
+ /*
+ * Returns 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);
+ }
+
+ #define TXTFMT_ZERO (1 << 0)
+ #define TXTFMT_SPACE (1 << 1)
+ #define TXTFMT_PLUS (1 << 2)
+ #define TXTFMT_MINUS (1 << 3)
+ #define TXTFMT_STAR_WIDTH (1 << 4)
+ #define TXTFMT_SHARP (1 << 5)
+ #define TXTFMT_WIDTH (1 << 6)
+ #define TXTFMT_PRECISION (1 << 7)
+ #define TXTFMT_STAR_PRECISION (1 << 8)
+
+ typedef struct FormatDesc
+ {
+ bits32 flags;
+ char field_type;
+ char quoting_type;
+ char lenmod;
+ int32 width;
+ int32 precision;
+ } FormatDesc;
+
+ static Datum
+ castValueTo(Datum value, Oid targetTypeId, Oid inputTypeId)
+ {
+ Oid funcId;
+ CoercionPathType pathtype;
+ FmgrInfo finfo;
+ Datum result;
+
+ if (inputTypeId != UNKNOWNOID)
+ pathtype = find_coercion_pathway(targetTypeId, inputTypeId,
+ COERCION_EXPLICIT,
+ &funcId);
+ else
+ pathtype = COERCION_PATH_COERCEVIAIO;
+
+ switch (pathtype)
+ {
+ case COERCION_PATH_RELABELTYPE:
+ result = value;
+ break;
+ case COERCION_PATH_FUNC:
+ {
+ Assert(OidIsValid(funcId));
+
+ fmgr_info(funcId, &finfo);
+ result = FunctionCall1(&finfo, value);
+ }
+ break;
+
+ case COERCION_PATH_COERCEVIAIO:
+ {
+ Oid typOutput;
+ Oid typinput;
+ bool typIsVarlena;
+ Oid typIOParam;
+ char *extval;
+
+ getTypeOutputInfo(inputTypeId, &typOutput, &typIsVarlena);
+ extval = OidOutputFunctionCall(typOutput, value);
+
+ getTypeInputInfo(targetTypeId, &typinput, &typIOParam);
+ result = OidInputFunctionCall(typinput, extval, typIOParam, -1);
+ }
+ break;
+
+ default:
+ elog(ERROR, "failed to find conversion function from %s to %s",
+ format_type_be(inputTypeId), format_type_be(targetTypeId));
+ /* be compiler quiet */
+ result = (Datum) 0;
+ }
+
+ return result;
+ }
+
+ #define CHECK_PAD(symbol, pad_value) \
+ do { \
+ if (desc->flags & (pad_value)) \
+ ereport(ERROR, \
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE), \
+ errmsg("invalid input for format function"), \
+ errdetail("Format string is '%s'.", TextDatumGetCString(fmt)), \
+ errhint("Symbol '%c' can be used only once.", (symbol)))); \
+ desc->flags |= (pad_value); \
+ } while(0);
+
+ /*
+ * parse and verify sprintf parameter
+ *
+ * %[flags][width][.precision]specifier
+ */
+ static const char *
+ parsePlaceholder(const char *src,
+ const char *end_ptr,
+ FormatDesc *desc,
+ text *fmt)
+ {
+ char c;
+
+ desc->field_type = '\0';
+ desc->quoting_type = '\0';
+ desc->lenmod = '\0';
+ desc->flags = 0;
+ desc->width = 0;
+ desc->precision = 0;
+
+ while (src < end_ptr && desc->field_type == '\0')
+ {
+ c = *++src;
+
+ /*
+ * sprintf supports the most common formats that has a sense for high
+ * level programming language with two additions - format tags "iq"
+ * and "lq". These tags can be used in sense "identifier qouted"
+ * and "literal quoted".
+ */
+ if (c == 'i' || c == 'l')
+ {
+ /* look forward - can be a proprietary tag */
+ if (src < end_ptr && src[1] == 'q')
+ {
+ desc->field_type = *++src;
+ desc->quoting_type = c;
+
+ continue;
+ }
+ }
+
+ switch (c)
+ {
+ case '0':
+ CHECK_PAD('0', TXTFMT_ZERO);
+ break;
+ case ' ':
+ CHECK_PAD(' ', TXTFMT_SPACE);
+ break;
+ case '+':
+ CHECK_PAD('+', TXTFMT_PLUS);
+ break;
+ case '-':
+ CHECK_PAD('-', TXTFMT_MINUS);
+ break;
+ case '*':
+ CHECK_PAD('*', TXTFMT_STAR_WIDTH);
+ break;
+ case '#':
+ CHECK_PAD('#', TXTFMT_SHARP);
+ break;
+ case 'o': case 'i': case 'e': case 'E': case 'f':
+ case 'g': case 'd': case 's': case 'x': case 'X': case 'q':
+ desc->field_type = *src;
+ break;
+ case '1': case '2': case '3': case '4':
+ case '5': case '6': case '7': case '8': case '9':
+ CHECK_PAD('9', TXTFMT_WIDTH);
+ desc->width = c - '0';
+ while (src < end_ptr && isdigit(src[1]))
+ desc->width = desc->width * 10 + *++src - '0';
+ break;
+ case '.':
+ if (src < end_ptr)
+ {
+ if (src[1] == '*')
+ {
+ CHECK_PAD('.', TXTFMT_STAR_PRECISION);
+ src++;
+ }
+ else
+ {
+ /*
+ * when no one digit is entered, then precision
+ * is zero - digits are optional.
+ */
+ CHECK_PAD('.', TXTFMT_PRECISION);
+ while (src < end_ptr && isdigit(src[1]))
+ desc->precision = desc->precision * 10 +
+ *++src - '0';
+ }
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid input for format function"),
+ errdetail("missing precision value")));
+ break;
+
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unsupported format tag '%c'", c)));
+ }
+ }
+
+ if (desc->field_type == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid input for format function")));
+
+ return src;
+ }
+
+ #define MAX_FORMAT_SIZE 32
+
+ static void
+ currentFormat(char str[], const FormatDesc *desc)
+ {
+ size_t len = 0;
+
+ str[len++] = '%';
+
+ if (desc->flags & TXTFMT_ZERO)
+ str[len++] = '0';
+
+ if (desc->flags & TXTFMT_MINUS)
+ str[len++] = '-';
+
+ if (desc->flags & TXTFMT_PLUS)
+ str[len++] = '+';
+
+ if (desc->flags & TXTFMT_SPACE)
+ str[len++] = ' ';
+
+ if (desc->flags & TXTFMT_SHARP)
+ str[len++] = '#';
+
+ if ((desc->flags & TXTFMT_WIDTH) ||
+ (desc->flags & TXTFMT_STAR_WIDTH))
+ str[len++] = '*';
+
+ if ((desc->flags & TXTFMT_PRECISION) ||
+ (desc->flags & TXTFMT_STAR_PRECISION))
+ {
+ memcpy(str + len, ".*", 2);
+ len += 2;
+ }
+
+ if (desc->lenmod == 'l')
+ {
+ size_t sz = strlen(INT64_FORMAT) - 2;
+ memcpy(str + len, INT64_FORMAT + 1, sz);
+ len += sz;
+ }
+ else if (desc->lenmod != '\0')
+ str[len++] = desc->lenmod;
+
+ str[len++] = desc->field_type;
+ str[len] = '\0';
+ }
+
+ /*
+ * simulate %+width.precion%s format of sformat function
+ */
+ static void
+ append_string(StringInfo str, FormatDesc *desc, const char *string)
+ {
+ int nchars = 0; /* length of substring in chars */
+ int binlen = 0; /* length of substring in bytes */
+
+ /*
+ * apply precision - it means "show only first n chars", for strings - this flag is
+ * ignored for proprietary tags %lq and iq, because we can't to show a first n chars
+ * from possible quoted value.
+ */
+ if (desc->flags & TXTFMT_PRECISION && desc->field_type != 'q')
+ {
+ const char *ptr = string;
+ int len = desc->precision;
+
+ if (pg_database_encoding_max_length() > 1)
+ {
+ while (*ptr && len > 0)
+ {
+ ptr += pg_mblen(ptr);
+ len--;
+ nchars++;
+ }
+ }
+ else
+ {
+ while (*ptr && len > 0)
+ {
+ ptr++;
+ len--;
+ nchars++;
+ }
+ }
+
+ binlen = ptr - string;
+ }
+ else
+ {
+ /* there isn't precion specified, show complete string */
+ nchars = pg_mbstrlen(string);
+ binlen = strlen(string);
+ }
+
+ /* when width is specified, then we have to solve left or right align */
+ if (desc->flags & TXTFMT_WIDTH)
+ {
+ if (desc->width > nchars)
+ {
+ /* add neccessary spaces to begin or end */
+ if (desc->flags & TXTFMT_MINUS)
+ {
+ /* allign to left */
+ appendBinaryStringInfo(str, string, binlen);
+ appendStringInfoSpaces(str, desc->width - nchars);
+ }
+ else
+ {
+ /* allign to right */
+ appendStringInfoSpaces(str, desc->width - nchars);
+ appendBinaryStringInfo(str, string, binlen);
+ }
+
+ }
+ else
+ /* just copy result to output */
+ appendBinaryStringInfo(str, string, binlen);
+ }
+ else
+ /* just copy result to output */
+ appendBinaryStringInfo(str, string, binlen);
+ }
+
+ /*
+ * Set width and precision when they are defined dynamicaly
+ */
+ static int
+ setWidthAndPrecision(FormatDesc *desc,
+ FunctionCallInfoData *fcinfo,
+ int current)
+ {
+
+ /*
+ * don't allow ambiguous definition
+ */
+ if ((desc->flags & TXTFMT_WIDTH) && (desc->flags & TXTFMT_STAR_WIDTH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid input for format function"),
+ errdetail("ambiguous width definition")));
+
+ if ((desc->flags & TXTFMT_PRECISION) && (desc->flags & TXTFMT_STAR_PRECISION))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid input for format function"),
+ errdetail("ambiguous precision definition")));
+ if (desc->flags & TXTFMT_STAR_WIDTH)
+ {
+ if (current >= PG_NARGS())
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too few parameters for format function")));
+
+ if (PG_ARGISNULL(current))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed"),
+ errhint("width (%dth) arguments is NULL", current)));
+
+ desc->width = DatumGetInt32(castValueTo(PG_GETARG_DATUM(current), INT4OID,
+ get_fn_expr_argtype(fcinfo->flinfo, current)));
+ /* reset flag */
+ desc->flags ^= TXTFMT_STAR_WIDTH;
+ desc->flags |= TXTFMT_WIDTH;
+ current += 1;
+ }
+
+ if (desc->flags & TXTFMT_STAR_PRECISION)
+ {
+ if (current >= PG_NARGS())
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too few parameters for format function")));
+
+ if (PG_ARGISNULL(current))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed"),
+ errhint("width (%dth) arguments is NULL", current)));
+
+ desc->precision = DatumGetInt32(castValueTo(PG_GETARG_DATUM(current), INT4OID,
+ get_fn_expr_argtype(fcinfo->flinfo, current)));
+ /* reset flags */
+ desc->flags ^= TXTFMT_STAR_PRECISION;
+ desc->flags |= TXTFMT_PRECISION;
+ current += 1;
+ }
+
+ return current;
+ }
+
+ /*
+ * Replaces % symbols with the external representation of the arguments.
+ *
+ * Syntax: text_format(format text [, arg "any"] ...) RETURNS text
+ */
+ Datum
+ text_format(PG_FUNCTION_ARGS)
+ {
+ text *fmt;
+ StringInfoData str;
+ int arg;
+ const char *start_ptr,
+ *end_ptr,
+ *cp;
+ text *result;
+
+ /* returns null when format string is null */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ fmt = PG_GETARG_TEXT_PP(0);
+ start_ptr = VARDATA_ANY(fmt);
+ end_ptr = start_ptr + VARSIZE_ANY_EXHDR(fmt) - 1;
+
+ initStringInfo(&str);
+
+ arg = 1;
+ for (cp = start_ptr; cp <= end_ptr; cp++)
+ {
+ Oid typOutput;
+ bool typIsVarlena;
+ Datum value;
+ Oid valtype;
+ FormatDesc desc;
+
+ if (cp[0] != '%')
+ {
+ appendStringInfoChar(&str, cp[0]);
+ continue;
+ }
+
+ if (cp < end_ptr && cp[1] == '%')
+ {
+ /* when cp is not pointer on last char, check %% */
+ appendStringInfoChar(&str, cp[1]);
+ cp++;
+ continue;
+ }
+
+ cp = parsePlaceholder(cp, end_ptr, &desc, fmt);
+ arg = setWidthAndPrecision(&desc, fcinfo, arg);
+
+ if (arg >= PG_NARGS())
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too few parameters for format function")));
+
+ /*
+ * NULL value is printed as in normal cases, but NULL for tag
+ * %lq case - similar to quote_nullable. NULL cannot be used as
+ * identifier - so exception is raised when tag is %iq.
+ */
+ if (PG_ARGISNULL(arg))
+ {
+ if (desc.field_type != 'q')
+ append_string(&str, &desc, "");
+ else if (desc.quoting_type == 'i')
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed"),
+ errhint("cannot to use NULL as identifier")));
+ else
+ append_string(&str, &desc, "NULL");
+ arg++;
+ continue;
+ }
+
+ /* append n-th value */
+ value = PG_GETARG_DATUM(arg);
+ valtype = get_fn_expr_argtype(fcinfo->flinfo, arg);
+
+ /* convert value to target type */
+ switch (desc.field_type)
+ {
+ case 'o': case 'd': case 'i': case 'x': case 'X':
+ {
+ int64 target_value;
+ char format[MAX_FORMAT_SIZE];
+
+ desc.lenmod = 'l';
+ target_value = DatumGetInt64(castValueTo(value,
+ INT8OID, valtype));
+ currentFormat(format, &desc);
+
+ if ((desc.flags & TXTFMT_WIDTH) &&
+ (desc.flags & TXTFMT_PRECISION))
+ appendStringInfo(&str, format, desc.width,
+ desc.precision, target_value);
+ else if (desc.flags & TXTFMT_WIDTH)
+ appendStringInfo(&str, format, desc.width,
+ target_value);
+ else if (desc.flags & TXTFMT_PRECISION)
+ appendStringInfo(&str, format, desc.precision,
+ target_value);
+ else
+ appendStringInfo(&str, format, target_value);
+ }
+ break;
+ case 'e': case 'f': case 'g': case 'G': case 'E':
+ {
+ float8 target_value;
+ char format[MAX_FORMAT_SIZE];
+
+ target_value = DatumGetFloat8(castValueTo(value,
+ FLOAT8OID, valtype));
+ currentFormat(format, &desc);
+
+ if ((desc.flags & TXTFMT_WIDTH) &&
+ (desc.flags & TXTFMT_PRECISION))
+ appendStringInfo(&str, format, desc.width,
+ desc.precision, target_value);
+ else if (desc.flags & TXTFMT_WIDTH)
+ appendStringInfo(&str, format, desc.width,
+ target_value);
+ else if (desc.flags & TXTFMT_PRECISION)
+ appendStringInfo(&str, format, desc.precision,
+ target_value);
+ else
+ appendStringInfo(&str, format, target_value);
+ }
+ break;
+ case 's':
+ {
+ char *target_value;
+
+ getTypeOutputInfo(valtype, &typOutput, &typIsVarlena);
+ target_value = OidOutputFunctionCall(typOutput, value);
+ append_string(&str, &desc, target_value);
+ pfree(target_value);
+ }
+ break;
+ case 'q':
+ {
+ char *target_value;
+
+ getTypeOutputInfo(valtype, &typOutput, &typIsVarlena);
+ target_value = OidOutputFunctionCall(typOutput, value);
+
+ if (desc.quoting_type == 'i')
+ {
+ const char *quoted_str;
+
+ quoted_str = quote_identifier(target_value);
+ append_string(&str, &desc, quoted_str);
+ }
+ else
+ {
+ text *txt;
+ text *quoted_txt;
+ char *quoted_str;
+
+ txt = cstring_to_text(target_value);
+ quoted_txt = DatumGetTextP(DirectFunctionCall1(quote_literal, PointerGetDatum(txt)));
+ quoted_str = text_to_cstring(quoted_txt);
+ append_string(&str, &desc, quoted_str);
+
+ pfree(quoted_str);
+ }
+ pfree(target_value);
+ }
+ break;
+ default:
+ elog(ERROR, "unknown format: %c", desc.field_type);
+ }
+ arg++;
+ }
+
+ /* check if all arguments are used */
+ if (arg != PG_NARGS())
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too many parameters for format function")));
+ result = cstring_to_text_with_len(str.data, str.len);
+
+ pfree(str.data);
+
+ PG_RETURN_TEXT_P(result);
+ }
+
+ /*
+ * Non-variadic version of text_format only to validate format string.
+ */
+ Datum
+ text_format_nv(PG_FUNCTION_ARGS)
+ {
+ return text_format(fcinfo);
+ }
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6036493..3547cee 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("I/O");
*** 2718,2723 ****
--- 2718,2737 ----
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 = 3037 ( 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 to text");
+ DATA(insert OID = 3038 ( 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 to text with separators");
+ DATA(insert OID = 3039 ( 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 = 3040 ( 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 = 3041 ( 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 = 3042 ( format PGNSP PGUID 12 1 0 2276 f f f f f s 2 0 25 "25 2276" "{25,2276}" "{i,v}" _null_ _null_ text_format _null_ _null_ _null_ ));
+ DESCR("format text message");
+ DATA(insert OID = 3043 ( format PGNSP PGUID 12 1 0 0 f f f f f s 1 0 25 "25" _null_ _null_ _null_ _null_ text_format_nv _null_ _null_ _null_ ));
+ DESCR("format text message");
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 0c92334..c45faf1 100644
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum string_agg_transfn(PG_FUNCT
*** 732,737 ****
--- 732,745 ----
extern Datum string_agg_delim_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);
+ extern Datum text_format(PG_FUNCTION_ARGS);
+ extern Datum text_format_nv(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 08d002f..faf9f91 100644
*** a/src/test/regress/expected/text.out
--- b/src/test/regress/expected/text.out
*************** ERROR: operator does not exist: integer
*** 51,53 ****
--- 51,255 ----
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.
+ -- format
+ select format('Hello %s %s', 'World', 10);
+ format
+ ----------------
+ Hello World 10
+ (1 row)
+
+ select format('%%%s%%, date: %s', 10, to_date('20080809','YYYYMMDD'));
+ format
+ ------------------------
+ %10%, date: 08-09-2008
+ (1 row)
+
+ select format('Hello');
+ format
+ --------
+ Hello
+ (1 row)
+
+ select format('Hello %s'); -- error
+ ERROR: too few parameters for format function
+ select format('Hello',10); -- error
+ ERROR: too many parameters for format function
+ select format('>>>%10s %10d<<<', 'hello', 10);
+ format
+ -----------------------------
+ >>> hello 10<<<
+ (1 row)
+
+ select format('>>>%-10s<<<', 'hello');
+ format
+ ------------------
+ >>>hello <<<
+ (1 row)
+
+ select format('>>>%5.2<<<', 'abcde'); -- error
+ ERROR: unsupported format tag '<'
+ select format('>>>%*s<<<', 10, 'abcdef');
+ format
+ ------------------
+ >>> abcdef<<<
+ (1 row)
+
+ select format('>>>%*s<<<', 10); -- error
+ ERROR: too few parameters for format function
+ select format('%010d', 10);
+ format
+ ------------
+ 0000000010
+ (1 row)
+
+ select format('%.6d', 10);
+ format
+ --------
+ 000010
+ (1 row)
+
+ select format('%'); -- error
+ ERROR: invalid input for format function
+ select format('%d', 100.0/3.0);
+ format
+ --------
+ 33
+ (1 row)
+
+ select format('%e', 100.0/3.0);
+ format
+ --------------
+ 3.333333e+01
+ (1 row)
+
+ select format('%f', 100.0/3.0);
+ format
+ -----------
+ 33.333333
+ (1 row)
+
+ select format('%g', 100.0/3.0);
+ format
+ ---------
+ 33.3333
+ (1 row)
+
+ select format('%7.4e', 100.0/3.0);
+ format
+ ------------
+ 3.3333e+01
+ (1 row)
+
+ select format('%7.4f', 100.0/3.0);
+ format
+ ---------
+ 33.3333
+ (1 row)
+
+ select format('%7.4g', 100.0/3.0);
+ format
+ ---------
+ 33.33
+ (1 row)
+
+ select format('%d', NULL);
+ format
+ --------
+
+ (1 row)
+
+ select format('some quotes: %q %lq %iq', 'tab', 'tab', 'tab');
+ format
+ ------------------------------
+ some quotes: 'tab' 'tab' tab
+ (1 row)
+
+ select format('some quotes: %q %lq %iq', 'a''"b', 'a''"b', 'a''"b');
+ format
+ --------------------------------------
+ some quotes: 'a''"b' 'a''"b' "a'""b"
+ (1 row)
+
+ select format('null literal: %lq', NULL);
+ format
+ --------------------
+ null literal: NULL
+ (1 row)
+
+ select format('null identifier: %iq', NULL); -- error
+ ERROR: null value not allowed
+ HINT: cannot to use NULL as identifier
+ /*
+ * concat tests
+ */
+ select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
+ concat
+ ----------------------
+ 123hellotf03-09-2010
+ (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; -- yes, have to be true
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ /*
+ * others tests
+ */
+ select reverse('abcde');
+ reverse
+ ---------
+ edcba
+ (1 row)
+
+ select i, left('ahoj', i) from generate_series(-5, 5) t(i) order by i;
+ i | left
+ ----+------
+ -5 |
+ -4 |
+ -3 | a
+ -2 | ah
+ -1 | aho
+ 0 |
+ 1 | a
+ 2 | ah
+ 3 | aho
+ 4 | ahoj
+ 5 | ahoj
+ (11 rows)
+
+ select i, right('ahoj', i) from generate_series(-5, 5) t(i) order by i;
+ i | right
+ ----+-------
+ -5 |
+ -4 |
+ -3 | j
+ -2 | oj
+ -1 | hoj
+ 0 |
+ 1 | j
+ 2 | oj
+ 3 | hoj
+ 4 | ahoj
+ 5 | ahoj
+ (11 rows)
+
diff --git a/src/test/regress/sql/text.sql b/src/test/regress/sql/text.sql
index b739e56..90d4e67 100644
*** a/src/test/regress/sql/text.sql
--- b/src/test/regress/sql/text.sql
*************** select 'four: ' || 2+2;
*** 28,30 ****
--- 28,75 ----
-- but not this:
select 3 || 4.0;
+
+ -- format
+ select format('Hello %s %s', 'World', 10);
+ select format('%%%s%%, date: %s', 10, to_date('20080809','YYYYMMDD'));
+ select format('Hello');
+ select format('Hello %s'); -- error
+ select format('Hello',10); -- error
+ select format('>>>%10s %10d<<<', 'hello', 10);
+ select format('>>>%-10s<<<', 'hello');
+ select format('>>>%5.2<<<', 'abcde'); -- error
+ select format('>>>%*s<<<', 10, 'abcdef');
+ select format('>>>%*s<<<', 10); -- error
+ select format('%010d', 10);
+ select format('%.6d', 10);
+ select format('%'); -- error
+
+ select format('%d', 100.0/3.0);
+ select format('%e', 100.0/3.0);
+ select format('%f', 100.0/3.0);
+ select format('%g', 100.0/3.0);
+ select format('%7.4e', 100.0/3.0);
+ select format('%7.4f', 100.0/3.0);
+ select format('%7.4g', 100.0/3.0);
+ select format('%d', NULL);
+
+ select format('some quotes: %q %lq %iq', 'tab', 'tab', 'tab');
+ select format('some quotes: %q %lq %iq', 'a''"b', 'a''"b', 'a''"b');
+ select format('null literal: %lq', NULL);
+ select format('null identifier: %iq', NULL); -- error
+
+ /*
+ * concat tests
+ */
+ select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
+ 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; -- yes, have to be true
+
+ /*
+ * others tests
+ */
+ select reverse('abcde');
+ select i, left('ahoj', i) from generate_series(-5, 5) t(i) order by i;
+ select i, right('ahoj', i) from generate_series(-5, 5) t(i) order by i;