*** ./contrib/stringfunc/expected/stringfunc.out.orig 2010-03-09 13:28:17.082096569 +0100 --- ./contrib/stringfunc/expected/stringfunc.out 2010-03-09 19:44:22.672219719 +0100 *************** *** 0 **** --- 1,160 ---- + SET client_min_messages = warning; + \set ECHO none + RESET client_min_messages; + -- sprintf test + select sprintf('>>>%10s %10d<<<', 'hello', 10); + sprintf + ----------------------------- + >>> hello 10<<< + (1 row) + + select sprintf('>>>%-10s<<<', 'hello'); + sprintf + ------------------ + >>>hello <<< + (1 row) + + select sprintf('%010d', 10); + sprintf + ------------ + 0000000010 + (1 row) + + select sprintf('%d', 100.0/3.0); + sprintf + --------- + 33 + (1 row) + + select sprintf('%e', 100.0/3.0); + sprintf + -------------- + 3.333333e+01 + (1 row) + + select sprintf('%f', 100.0/3.0); + sprintf + ----------- + 33.333333 + (1 row) + + select sprintf('%g', 100.0/3.0); + sprintf + --------- + 33.3333 + (1 row) + + select sprintf('%7.4e', 100.0/3.0); + sprintf + ------------ + 3.3333e+01 + (1 row) + + select sprintf('%7.4f', 100.0/3.0); + sprintf + --------- + 33.3333 + (1 row) + + select sprintf('%7.4g', 100.0/3.0); + sprintf + --------- + 33.33 + (1 row) + + /* + * 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_sql(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); + concat_sql + -------------------------------- + 1,2,3,'hello',t,f,'03-09-2010' + (1 row) + + select concat_json(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); + concat_json + --------------------------------------- + 1,2,3,"hello",true,false,"03-09-2010" + (1 row) + + /* + * others tests + */ + select rvrs('abcde'); + rvrs + ------- + edcba + (1 row) + + select left('ahoj', 2); + left + ------ + ah + (1 row) + + select left('ahoj', 5); + left + ------ + ahoj + (1 row) + + select left('ahoj', 0); + left + ------ + + (1 row) + + select left('ahoj', -1); + left + ------ + aho + (1 row) + + select left('ahoj', -10); + left + ------ + + (1 row) + + select right('ahoj', 2); + right + ------- + oj + (1 row) + + select right('ahoj', 5); + right + ------- + ahoj + (1 row) + + select right('ahoj', 0); + right + ------- + + (1 row) + + select right('ahoj', -1); + right + ------- + hoj + (1 row) + + select right('ahoj', -10); + right + ------- + + (1 row) + *** ./contrib/stringfunc/Makefile.orig 2010-03-05 15:05:20.171741922 +0100 --- ./contrib/stringfunc/Makefile 2010-03-05 14:34:31.391738508 +0100 *************** *** 0 **** --- 1,17 ---- + # $PostgreSQL: pgsql/contrib/stringfunc/Makefile,v 1.1 2008/07/29 18:31:20 tgl Exp $ + + MODULES = stringfunc + DATA_built = stringfunc.sql + DATA = uninstall_stringfunc.sql + REGRESS = stringfunc + + ifdef USE_PGXS + PG_CONFIG = pg_config + PGXS := $(shell $(PG_CONFIG) --pgxs) + include $(PGXS) + else + subdir = contrib/stringfunc + top_builddir = ../.. + include $(top_builddir)/src/Makefile.global + include $(top_srcdir)/contrib/contrib-global.mk + endif *** ./contrib/stringfunc/sql/stringfunc.sql.orig 2010-03-09 13:14:14.064096132 +0100 --- ./contrib/stringfunc/sql/stringfunc.sql 2010-03-09 14:02:05.999094713 +0100 *************** *** 0 **** --- 1,44 ---- + + SET client_min_messages = warning; + \set ECHO none + \i stringfunc.sql + \set ECHO all + RESET client_min_messages; + + -- sprintf test + select sprintf('>>>%10s %10d<<<', 'hello', 10); + select sprintf('>>>%-10s<<<', 'hello'); + select sprintf('%010d', 10); + + select sprintf('%d', 100.0/3.0); + select sprintf('%e', 100.0/3.0); + select sprintf('%f', 100.0/3.0); + select sprintf('%g', 100.0/3.0); + select sprintf('%7.4e', 100.0/3.0); + select sprintf('%7.4f', 100.0/3.0); + select sprintf('%7.4g', 100.0/3.0); + + /* + * 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_sql(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); + select concat_json(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD')); + + /* + * others tests + */ + select rvrs('abcde'); + + select left('ahoj', 2); + select left('ahoj', 5); + select left('ahoj', 0); + select left('ahoj', -1); + select left('ahoj', -10); + + select right('ahoj', 2); + select right('ahoj', 5); + select right('ahoj', 0); + select right('ahoj', -1); + select right('ahoj', -10); *** ./contrib/stringfunc/stringfunc.c.orig 2010-03-05 15:05:30.876738040 +0100 --- ./contrib/stringfunc/stringfunc.c 2010-07-08 13:04:13.458422094 +0200 *************** *** 0 **** --- 1,1020 ---- + #include "postgres.h" + #include "stdio.h" + #include "wchar.h" + + #include "catalog/pg_type.h" + #include "lib/stringinfo.h" + #include "mb/pg_wchar.h" + #include "parser/parse_coerce.h" + #include "utils/builtins.h" + #include "utils/lsyscache.h" + #include "utils/memutils.h" + #include "utils/pg_locale.h" + + PG_MODULE_MAGIC; + + #define CHECK_PAD(symbol, pad_value) \ + do { \ + if (pdesc->flags & pad_value) \ + ereport(ERROR, \ + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), \ + errmsg("broken sprintf format"), \ + errdetail("Format string is '%s'.", TextDatumGetCString(fmt)), \ + errhint("Symbol '%c' can be used only one time.", symbol))); \ + pdesc->flags |= pad_value; \ + } while(0); + + /* + * string functions + */ + Datum stringfunc_sprintf(PG_FUNCTION_ARGS); + Datum stringfunc_sprintf_nv(PG_FUNCTION_ARGS); + Datum stringfunc_concat(PG_FUNCTION_ARGS); + Datum stringfunc_concat_ws(PG_FUNCTION_ARGS); + Datum stringfunc_concat_json(PG_FUNCTION_ARGS); + Datum stringfunc_concat_sql(PG_FUNCTION_ARGS); + Datum stringfunc_left(PG_FUNCTION_ARGS); + Datum stringfunc_right(PG_FUNCTION_ARGS); + Datum stringfunc_left(PG_FUNCTION_ARGS); + Datum stringfunc_rvrs(PG_FUNCTION_ARGS); + + /* + * V1 registrations + */ + PG_FUNCTION_INFO_V1(stringfunc_sprintf); + PG_FUNCTION_INFO_V1(stringfunc_sprintf_nv); + PG_FUNCTION_INFO_V1(stringfunc_concat); + PG_FUNCTION_INFO_V1(stringfunc_concat_ws); + PG_FUNCTION_INFO_V1(stringfunc_concat_json); + PG_FUNCTION_INFO_V1(stringfunc_concat_sql); + PG_FUNCTION_INFO_V1(stringfunc_rvrs); + PG_FUNCTION_INFO_V1(stringfunc_left); + PG_FUNCTION_INFO_V1(stringfunc_right); + + typedef enum { + stringfunc_ZERO = 1, + stringfunc_SPACE = 2, + stringfunc_PLUS = 4, + stringfunc_MINUS = 8, + stringfunc_STAR_WIDTH = 16, + stringfunc_SHARP = 32, + stringfunc_WIDTH = 64, + stringfunc_PRECISION = 128, + stringfunc_STAR_PRECISION = 256 + } PlaceholderTags; + + typedef struct { + int flags; + char field_type; + char lenmod; + int32 width; + int32 precision; + } FormatPlaceholderData; + + typedef FormatPlaceholderData *PlaceholderDesc; + + /* + * Static functions + */ + static char *json_string(char *str); + static int mb_string_info(text *str, char **sizes, int **positions); + + /* + * External + */ + extern PGDLLIMPORT char *days[]; + + + 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; + } + + /* + * parse and verify sprintf parameter + * + * %[flags][width][.precision]specifier + * + */ + static char * + parsePlaceholder(char *src, char *end_ptr, PlaceholderDesc pdesc, text *fmt) + { + char c; + + pdesc->field_type = '\0'; + pdesc->lenmod = '\0'; + pdesc->flags = 0; + pdesc->width = 0; + pdesc->precision = 0; + + while (src < end_ptr && pdesc->field_type == '\0') + { + c = *++src; + + switch (c) + { + case '0': + CHECK_PAD('0', stringfunc_ZERO); + break; + case ' ': + CHECK_PAD(' ', stringfunc_SPACE); + break; + case '+': + CHECK_PAD('+', stringfunc_PLUS); + break; + case '-': + CHECK_PAD('-', stringfunc_MINUS); + break; + case '*': + CHECK_PAD('*', stringfunc_STAR_WIDTH); + break; + case '#': + CHECK_PAD('#', stringfunc_SHARP); + break; + case 'o': case 'i': case 'e': case 'E': case 'f': + case 'g': case 'd': case 's': case 'x': case 'X': + pdesc->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', stringfunc_WIDTH); + pdesc->width = c - '0'; + while (src < end_ptr && isdigit(src[1])) + pdesc->width = pdesc->width * 10 + *++src - '0'; + break; + case '.': + if (src < end_ptr) + { + if (src[1] == '*') + { + CHECK_PAD('.', stringfunc_STAR_PRECISION); + src++; + elog(NOTICE, "1"); + } + else + { + bool valid = false; + + CHECK_PAD('.', stringfunc_PRECISION); + while (src < end_ptr && isdigit(src[1])) + { + pdesc->precision = pdesc->precision * 10 + *++src - '0'; + valid = true; + } + + if (!valid) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("broken sprinf format"), + errdetail("missing precision value"))); + } + } + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("broken sprinf format"), + errdetail("missing precision value"))); + break; + + default: + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unsupported sprintf format tag '%c'", c))); + } + } + + if (pdesc->field_type == '\0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("broken sprintf format"))); + + return src; + } + + static char * + currentFormat(StringInfo str, PlaceholderDesc pdesc) + { + resetStringInfo(str); + appendStringInfoChar(str,'%'); + + if (pdesc->flags & stringfunc_ZERO) + appendStringInfoChar(str, '0'); + + if (pdesc->flags & stringfunc_MINUS) + appendStringInfoChar(str, '-'); + + if (pdesc->flags & stringfunc_PLUS) + appendStringInfoChar(str, '+'); + + if (pdesc->flags & stringfunc_SPACE) + appendStringInfoChar(str, ' '); + + if (pdesc->flags & stringfunc_SHARP) + appendStringInfoChar(str, '#'); + + if ((pdesc->flags & stringfunc_WIDTH) || (pdesc->flags & stringfunc_STAR_WIDTH)) + appendStringInfoChar(str, '*'); + + if ((pdesc->flags & stringfunc_PRECISION) || (pdesc->flags & stringfunc_STAR_PRECISION)) + appendStringInfoString(str, ".*"); + + if (pdesc->lenmod != '\0') + appendStringInfoChar(str, pdesc->lenmod); + + appendStringInfoChar(str, pdesc->field_type); + + return str->data; + } + + /* + * Set width and precision when they are defined dynamicaly + */ + static + int setWidthAndPrecision(PlaceholderDesc pdesc, FunctionCallInfoData *fcinfo, int current) + { + + /* + * don't allow ambiguous definition + */ + if ((pdesc->flags & stringfunc_WIDTH) && (pdesc->flags & stringfunc_STAR_WIDTH)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("broken sprintf format"), + errdetail("ambiguous width definition"))); + + if ((pdesc->flags & stringfunc_PRECISION) && (pdesc->flags & stringfunc_STAR_PRECISION)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("broken sprintf format"), + errdetail("ambiguous precision definition"))); + if (pdesc->flags & stringfunc_STAR_WIDTH) + { + if (current >= PG_NARGS()) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("too few parameters specified for printf 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))); + + pdesc->width = DatumGetInt32(castValueTo(PG_GETARG_DATUM(current), INT4OID, + get_fn_expr_argtype(fcinfo->flinfo, current))); + /* reset flag */ + pdesc->flags ^= stringfunc_STAR_WIDTH; + pdesc->flags |= stringfunc_WIDTH; + current += 1; + } + + if (pdesc->flags & stringfunc_STAR_PRECISION) + { + if (current >= PG_NARGS()) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("too few parameters specified for printf 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))); + + pdesc->precision = DatumGetInt32(castValueTo(PG_GETARG_DATUM(current), INT4OID, + get_fn_expr_argtype(fcinfo->flinfo, current))); + /* reset flags */ + pdesc->flags ^= stringfunc_STAR_PRECISION; + pdesc->flags |= stringfunc_PRECISION; + current += 1; + } + + return current; + } + + /* + * sprintf function - it is wrapper for libc vprintf function + * + * ensure PostgreSQL -> C casting + */ + Datum + stringfunc_sprintf(PG_FUNCTION_ARGS) + { + text *fmt; + StringInfoData str; + StringInfoData format_str; + char *cp; + int i = 1; + size_t len; + char *start_ptr, + *end_ptr; + FormatPlaceholderData pdesc; + text *result; + + /* When format string is null, returns null */ + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + fmt = PG_GETARG_TEXT_PP(0); + len = VARSIZE_ANY_EXHDR(fmt); + start_ptr = VARDATA_ANY(fmt); + end_ptr = start_ptr + len - 1; + + initStringInfo(&str); + initStringInfo(&format_str); + + for (cp = start_ptr; cp <= end_ptr; cp++) + { + if (cp[0] == '%') + { + /* when cp is not pointer on last char, check %% */ + if (cp < end_ptr && cp[1] == '%') + { + appendStringInfoChar(&str, cp[1]); + cp++; + continue; + } + + if (i >= PG_NARGS()) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("too few parameters specified for printf function"))); + + cp = parsePlaceholder(cp, end_ptr, &pdesc, fmt); + i = setWidthAndPrecision(&pdesc, fcinfo, i); + + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + + /* append n-th value */ + value = PG_GETARG_DATUM(i); + valtype = get_fn_expr_argtype(fcinfo->flinfo, i); + + /* convert value to target type */ + switch (pdesc.field_type) + { + case 'o': case 'd': case 'i': case 'x': case 'X': + { + int64 target_value; + const char *format; + + pdesc.lenmod = 'l'; + target_value = DatumGetInt64(castValueTo(value, INT8OID, valtype)); + format = currentFormat(&format_str, &pdesc); + + if ((pdesc.flags & stringfunc_WIDTH) && (pdesc.flags & stringfunc_PRECISION)) + appendStringInfo(&str, format, pdesc.width, pdesc.precision, target_value); + else if (pdesc.flags & stringfunc_WIDTH) + appendStringInfo(&str, format, pdesc.width, target_value); + else if (pdesc.flags & stringfunc_PRECISION) + appendStringInfo(&str, format, pdesc.precision, target_value); + else + appendStringInfo(&str, format, target_value); + } + break; + case 'e': case 'f': case 'g': case 'G': case 'E': + { + float8 target_value; + const char *format; + + target_value = DatumGetFloat8(castValueTo(value, FLOAT8OID, valtype)); + format = currentFormat(&format_str, &pdesc); + + if ((pdesc.flags & stringfunc_WIDTH) && (pdesc.flags & stringfunc_PRECISION)) + appendStringInfo(&str, format, pdesc.width, pdesc.precision, target_value); + else if (pdesc.flags & stringfunc_WIDTH) + appendStringInfo(&str, format, pdesc.width, target_value); + else if (pdesc.flags & stringfunc_PRECISION) + appendStringInfo(&str, format, pdesc.precision, target_value); + else + appendStringInfo(&str, format, target_value); + } + break; + case 's': + { + char *target_value; + const char *format; + Oid typoutput; + bool typIsVarlena; + + getTypeOutputInfo(valtype, &typoutput, &typIsVarlena); + target_value = OidOutputFunctionCall(typoutput, value); + + format = currentFormat(&format_str, &pdesc); + + /* use wide chars if it is necessary */ + if (pg_database_encoding_max_length() > 1) + { + wchar_t *wformat; + wchar_t *wbuffer; + size_t fmtlen = (strlen(format) + 1) * sizeof(wchar_t); + size_t len = strlen(target_value) + 1; + + wformat = palloc(fmtlen); + char2wchar(wformat, fmtlen, format, strlen(format)); + wbuffer = palloc(len * sizeof(wchar_t)); + + for (;;) + { + int result; + + if ((pdesc.flags & stringfunc_WIDTH) && (pdesc.flags & stringfunc_PRECISION)) + result = swprintf(wbuffer, len, wformat, pdesc.width, + pdesc.precision, target_value); + else if (pdesc.flags & stringfunc_WIDTH) + result = swprintf(wbuffer, len, wformat, pdesc.width, target_value); + else if (pdesc.flags & stringfunc_PRECISION) + result = swprintf(wbuffer, len, wformat, pdesc.precision, target_value); + else + result = swprintf(wbuffer, len, wformat, target_value); + + if (result != -1) + { + /* append result */ + appendStringInfo(&str, "%ls", wbuffer); + break; + } + else + { + /* increase buffer size and repeat */ + len *= 2; + if ((len * sizeof(pg_wchar)) > MaxAllocSize) + ereport(ERROR, + (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), + errmsg("out of memory"))); + + wbuffer = repalloc(wbuffer, len * sizeof(wchar_t) + 1); + /* continue */ + } + } + + pfree(wbuffer); + pfree(wformat); + } + else + { + /* shortcut for one byte encoding */ + if ((pdesc.flags & stringfunc_WIDTH) && (pdesc.flags & stringfunc_PRECISION)) + appendStringInfo(&str, format, pdesc.width, pdesc.precision, target_value); + else if (pdesc.flags & stringfunc_WIDTH) + appendStringInfo(&str, format, pdesc.width, target_value); + else if (pdesc.flags & stringfunc_PRECISION) + appendStringInfo(&str, format, pdesc.precision, target_value); + else + appendStringInfo(&str, format, target_value); + + pfree(target_value); + } + } + break; + } + } + else + /* return null when some argument is null */ + PG_RETURN_NULL(); + i++; + } + else + appendStringInfoChar(&str, cp[0]); + } + + /* check if all arguments are used */ + if (i != PG_NARGS()) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("too many parameters"))); + result = cstring_to_text_with_len(str.data, str.len); + + pfree(str.data); + pfree(format_str.data); + + PG_RETURN_TEXT_P(result); + } + + /* + * only wrapper + */ + Datum + stringfunc_sprintf_nv(PG_FUNCTION_ARGS) + { + return stringfunc_sprintf(fcinfo); + } + + /* + * Concat values to comma separated list. This function + * is NULL safe. NULL values are skipped. + */ + Datum + stringfunc_concat(PG_FUNCTION_ARGS) + { + StringInfoData str; + int i; + text *result; + + /* return NULL, if there are not any parameter */ + if (PG_NARGS() == 0) + PG_RETURN_NULL(); + + initStringInfo(&str); + + for(i = 0; i < PG_NARGS(); i++) + { + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + Oid typoutput; + bool typIsVarlena; + + /* 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); + + PG_RETURN_TEXT_P(result); + } + + /* + * Concat values. First argument is separator. This function + * is NULL safe. NULL values are skipped. + */ + Datum + stringfunc_concat_ws(PG_FUNCTION_ARGS) + { + StringInfoData str; + text *result; + char *sepstr; + int i; + + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + /* return NULL, if there are not any parameter */ + if (PG_NARGS() == 1) + PG_RETURN_NULL(); + + sepstr = text_to_cstring(PG_GETARG_TEXT_P(0)); + initStringInfo(&str); + + for(i = 1; i < PG_NARGS(); i++) + { + if (i > 1) + appendStringInfoString(&str, sepstr); + + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + Oid typoutput; + bool typIsVarlena; + + /* 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); + + PG_RETURN_TEXT_P(result); + } + + /* + * Concat string with respect to SQL format. This is NULL safe. + * NULLs values are transformated to "NULL" string. + */ + Datum + stringfunc_concat_sql(PG_FUNCTION_ARGS) + { + StringInfoData str; + text *result; + int i; + + /* return NULL, if there are not any parameter */ + if (PG_NARGS() == 0) + PG_RETURN_NULL(); + + initStringInfo(&str); + + for(i = 0; i < PG_NARGS(); i++) + { + if (i > 0) + appendStringInfoChar(&str, ','); + + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + Oid typoutput; + bool typIsVarlena; + TYPCATEGORY typcat; + + /* append n-th value */ + value = PG_GETARG_DATUM(i); + valtype = get_fn_expr_argtype(fcinfo->flinfo, i); + typcat = TypeCategory(valtype); + + if (typcat == 'N' || typcat == 'B') + { + getTypeOutputInfo(valtype, &typoutput, &typIsVarlena); + appendStringInfoString(&str, OidOutputFunctionCall(typoutput, value)); + } + else + { + text *txt; + text *quoted_txt; + + getTypeOutputInfo(valtype, &typoutput, &typIsVarlena); + + /* get text value and quotize */ + txt = cstring_to_text(OidOutputFunctionCall(typoutput, value)); + quoted_txt = DatumGetTextP(DirectFunctionCall1(quote_literal, + PointerGetDatum(txt))); + appendStringInfoString(&str, text_to_cstring(quoted_txt)); + } + } + else + appendStringInfoString(&str, "NULL"); + } + + result = cstring_to_text_with_len(str.data, str.len); + pfree(str.data); + + PG_RETURN_TEXT_P(result); + } + + + /* + * Concat string with respect to JSON format. This is NULL safe. + * NULLs values are transformated to "null" string. + * JSON uses lowercase characters for boolean constants - see www.json.org + */ + Datum + stringfunc_concat_json(PG_FUNCTION_ARGS) + { + StringInfoData str; + text *result; + int i; + + /* return NULL, if there are not any parameter */ + if (PG_NARGS() == 0) + PG_RETURN_NULL(); + + initStringInfo(&str); + + for(i = 0; i < PG_NARGS(); i++) + { + if (i > 0) + appendStringInfoChar(&str, ','); + + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + Oid typoutput; + bool typIsVarlena; + TYPCATEGORY typcat; + + /* append n-th value */ + value = PG_GETARG_DATUM(i); + valtype = get_fn_expr_argtype(fcinfo->flinfo, i); + typcat = TypeCategory(valtype); + + if (typcat == 'N') + { + getTypeOutputInfo(valtype, &typoutput, &typIsVarlena); + appendStringInfoString(&str, OidOutputFunctionCall(typoutput, value)); + } else if (typcat == 'B') + { + bool bvalue = PG_GETARG_BOOL(i); + + appendStringInfoString(&str, bvalue ? "true" : "false"); + } + else + { + getTypeOutputInfo(valtype, &typoutput, &typIsVarlena); + appendStringInfo(&str, "\"%s\"", json_string(OidOutputFunctionCall(typoutput, value))); + } + } + else + appendStringInfoString(&str, "null"); + } + + result = cstring_to_text_with_len(str.data, str.len); + pfree(str.data); + + PG_RETURN_TEXT_P(result); + } + + + /* + * Returns first n chars. When n is negative, then + * it returns chars from n+1 position. + */ + Datum + stringfunc_left(PG_FUNCTION_ARGS) + { + text *str = PG_GETARG_TEXT_PP(0); + int len = VARSIZE_ANY_EXHDR(str); + char *p = VARDATA_ANY(str); + text *result; + int n = PG_GETARG_INT32(1); + + if (len == 0 || n == 0) + PG_RETURN_TEXT_P(cstring_to_text("")); + + if (pg_database_encoding_max_length() > 1) + { + char *sizes; + int *positions; + + len = mb_string_info(str, &sizes, &positions); + + if (n > 0) + { + n = n > len ? len : n; + result = cstring_to_text_with_len(p, positions[n - 1] + sizes[n - 1]); + } + else + { + n = -n > len ? len : -n; + result = cstring_to_text_with_len(p, positions[len - n - 1] + sizes[len - n - 1]); + } + + pfree(positions); + pfree(sizes); + } + else + { + if (n > 0) + { + n = n > len ? len : n; + result = cstring_to_text_with_len(p, n); + } + else + { + n = -n > len ? len : -n; + result = cstring_to_text_with_len(p, len - n); + } + } + + PG_RETURN_TEXT_P(result); + } + + /* + * Returns last n chars from string. When n is negative, + * then returns string without last n chars. + */ + Datum + stringfunc_right(PG_FUNCTION_ARGS) + { + text *str = PG_GETARG_TEXT_PP(0); + int len = VARSIZE_ANY_EXHDR(str); + char *p = VARDATA_ANY(str); + text *result; + int n = PG_GETARG_INT32(1); + + if (len == 0 || n == 0) + PG_RETURN_TEXT_P(CStringGetTextDatum("")); + + if (pg_database_encoding_max_length() > 1) + { + char *sizes; + int *positions; + + len = mb_string_info(str, &sizes, &positions); + + if (n > 0) + { + n = n > len ? len : n; + result = cstring_to_text_with_len(p + positions[len - n], + positions[len - 1] + sizes[len - 1] - positions[len - n]); + } + else + { + if (-n < len) + { + n = -n > len ? len : -n; + result = cstring_to_text_with_len(p + positions[n], + positions[len - 1] + sizes[len - 1] - positions[n]); + } + else + { + /* return empty string */ + result = cstring_to_text_with_len("", 0); + } + } + + pfree(positions); + pfree(sizes); + } + else + { + if (n > 0) + { + n = n > len ? len : n; + result = cstring_to_text_with_len(p + len - n, n); + } + else + { + n = -n > len ? len : -n; + result = cstring_to_text_with_len(p + n, len - n); + } + } + + PG_RETURN_TEXT_P(result); + } + + /* + * Returns reversed string + */ + Datum + stringfunc_rvrs(PG_FUNCTION_ARGS) + { + text *str; + text *result; + char *p; + int len; + char *data; + int i; + + str = PG_GETARG_TEXT_PP(0); + p = VARDATA_ANY(str); + len = VARSIZE_ANY_EXHDR(str); + + result = palloc(len + VARHDRSZ); + data = (char*) VARDATA(result); + SET_VARSIZE(result, len + VARHDRSZ); + + if (pg_database_encoding_max_length() > 1) + { + char *sizes; + int *positions; + + /* multibyte version */ + len = mb_string_info(str, &sizes, &positions); + for (i = len - 1; i >= 0; i--) + { + memcpy(data, p + positions[i], sizes[i]); + data += sizes[i]; + } + + pfree(positions); + pfree(sizes); + } + else + { + /* single byte version */ + for (i = len - 1; i >= 0; i--) + *data++ = p[i]; + } + + PG_RETURN_TEXT_P(result); + } + + /* + * Convert C string to JSON string + */ + static char * + json_string(char *str) + { + char len = strlen(str); + char *result, *wc; + + wc = result = palloc(len * 2 + 1); + while (*str != '\0') + { + char c = *str++; + + switch (c) + { + case '\t': + *wc++ = '\\'; + *wc++ = 't'; + break; + case '\b': + *wc++ = '\\'; + *wc++ = 'b'; + break; + case '\n': + *wc++ = '\\'; + *wc++ = 'n'; + break; + case '\r': + *wc++ = '\\'; + *wc++ = 'r'; + break; + case '\\': + *wc++ = '\\'; + *wc++ = '\\'; + break; + case '"': + *wc++ = '\\'; + *wc++ = '"'; + break; + default: + *wc++ = c; + } + } + *wc = '\0'; + return result; + } + + /* + * Returns length of string, size and position of every + * multibyte char in string. + */ + static int + mb_string_info(text *str, char **sizes, int **positions) + { + int r_len; + int cur_size = 0; + int sz; + char *p; + int cur = 0; + + p = VARDATA_ANY(str); + r_len = VARSIZE_ANY_EXHDR(str); + + if (NULL != sizes) + *sizes = palloc(r_len * sizeof(char)); + if (NULL != positions) + *positions = palloc(r_len * sizeof(int)); + + while (cur < r_len) + { + sz = pg_mblen(p); + if (sizes) + (*sizes)[cur_size] = sz; + if (positions) + (*positions)[cur_size] = cur; + cur += sz; + p += sz; + cur_size += 1; + } + + return cur_size; + } *** ./contrib/stringfunc/stringfunc.sql.in.orig 2010-07-08 11:27:31.877299385 +0200 --- ./contrib/stringfunc/stringfunc.sql.in 2010-03-09 13:27:16.618094628 +0100 *************** *** 0 **** --- 1,44 ---- + CREATE OR REPLACE FUNCTION sprintf(fmt text, VARIADIC args "any") + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_sprintf' + LANGUAGE C IMMUTABLE; + + CREATE OR REPLACE FUNCTION sprintf(fmt text) + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_sprintf_nv' + LANGUAGE C IMMUTABLE; + + CREATE OR REPLACE FUNCTION concat(VARIADIC args "any") + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_concat' + LANGUAGE C IMMUTABLE; + + CREATE OR REPLACE FUNCTION concat_ws(separator text, VARIADIC args "any") + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_concat_ws' + LANGUAGE C IMMUTABLE; + + CREATE OR REPLACE FUNCTION concat_json(VARIADIC args "any") + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_concat_json' + LANGUAGE C IMMUTABLE; + + CREATE OR REPLACE FUNCTION concat_sql(VARIADIC args "any") + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_concat_sql' + LANGUAGE C IMMUTABLE; + + CREATE OR REPLACE FUNCTION rvrs(str text) + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_rvrs' + LANGUAGE C IMMUTABLE STRICT; + + CREATE OR REPLACE FUNCTION left(str text, n int) + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_left' + LANGUAGE C IMMUTABLE STRICT; + + CREATE OR REPLACE FUNCTION right(str text, n int) + RETURNS text + AS 'MODULE_PATHNAME','stringfunc_right' + LANGUAGE C IMMUTABLE STRICT; *** ./contrib/stringfunc/uninstall_stringfunc.sql.orig 2010-03-05 15:05:47.260738126 +0100 --- ./contrib/stringfunc/uninstall_stringfunc.sql 2010-03-09 13:27:00.146096268 +0100 *************** *** 0 **** --- 1,9 ---- + DROP FUNCTION sprintf(fmt text, VARIADIC args "any"); + DROP FUNCTION sprintf(fmt text); + DROP FUNCTION concat(VARIADIC args "any"); + DROP FUNCTION concat_ws(separator text, VARIADIC args "any"); + DROP FUNCTION concat_json(VARIADIC args "any"); + DROP FUNCTION concat_sql(VARIADIC args "any"); + DROP FUNCTION rvrs(str text); + DROP FUNCTION left(str text, n int); + DROP FUNCTION right(str text, n int); *** ./doc/src/sgml/contrib.sgml.orig 2010-01-29 00:59:52.000000000 +0100 --- ./doc/src/sgml/contrib.sgml 2010-03-05 15:12:18.861738045 +0100 *************** *** 113,118 **** --- 113,119 ---- &seg; &contrib-spi; &sslinfo; + &stringfunc; &tablefunc; &test-parser; &tsearch2; *** ./doc/src/sgml/filelist.sgml.orig 2010-02-22 12:47:30.000000000 +0100 --- ./doc/src/sgml/filelist.sgml 2010-03-09 15:04:46.210861483 +0100 *************** *** 123,128 **** --- 123,129 ---- + *** ./doc/src/sgml/func.sgml.orig 2010-03-03 23:28:42.000000000 +0100 --- ./doc/src/sgml/func.sgml 2010-03-05 14:28:25.807765920 +0100 *************** *** 1262,1267 **** --- 1262,1270 ---- encode + format + + initcap *************** *** 1450,1455 **** --- 1453,1473 ---- + + format(formatstr text + [, value any [...] ]) + + text + + The format string can be followed by optional argument values to be inserted into + the result string. Inside the format string, % is replaced by the string representation + of the next optional argument's value. Write %% to emit a literal %. + + format('% % xxx: %', 10, 'foo', current_date) + 10 foo xxx: 2010-03-05 + + + initcap(string) text *** ./doc/src/sgml/stringfunc.sgml.orig 2010-03-05 15:11:23.284738364 +0100 --- ./doc/src/sgml/stringfunc.sgml 2010-03-09 15:10:43.378094796 +0100 *************** *** 0 **** --- 1,79 ---- + + + + stringfunc + + + stringfunc + + + + The stringfunc module provides a additional function + for operation over strings. These functions can be used as patter + for developing a variadic functions. + + + + How to Use It + + + Here's a simple example of usage: + + + SELECT sprintf('formated number: %10d',10); + + + + + Nodul contains following functions: + + + + + + sprintf(formatstr [, params]) clasic sprintf function + + + + + concat(param1 [, param2 [,...]]) a concation two or more strings + + + + + concat_ws(separator, param1 [, param2 [,...]])() a concation two + or more strings. First parameter is used as separator. + + + + + concat_json(param1 [, param2 [,...]]) a concation two or more + strings. Values are converted to JSON format. + + + + + concat_sql(param1 [, param2 [,...]]) a concation two or more + strings. Values are converted to format of SQL constants. + + + + + rvrs(str) reverse a string + + + + + left(str, n) returns n chars from start. When n is negative, then + returns chars without last n chars. + + + + + right(str, n) return last n chars. When n is negative, then + returns chars without first n chars. + + + + + *** ./src/backend/utils/adt/varlena.c.orig 2010-02-26 03:01:10.000000000 +0100 --- ./src/backend/utils/adt/varlena.c 2010-07-08 13:06:52.372551690 +0200 *************** *** 3415,3417 **** --- 3415,3504 ---- else PG_RETURN_NULL(); } + + /* + * Text format - a variadic function replaces % symbols with entered text. + */ + Datum + text_format(PG_FUNCTION_ARGS) + { + text *fmt; + StringInfoData str; + char *cp; + int i = 1; + size_t len; + char *start_ptr, + *end_ptr; + text *result; + + /* When format string is null, returns null */ + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + fmt = PG_GETARG_TEXT_PP(0); + len = VARSIZE_ANY_EXHDR(fmt); + start_ptr = VARDATA_ANY(fmt); + end_ptr = start_ptr + len - 1; + + initStringInfo(&str); + for (cp = start_ptr; cp <= end_ptr; cp++) + { + if (cp[0] == '%') + { + + /* when cp is not pointer on last char, check %% */ + if (cp < end_ptr && cp[1] == '%') + { + appendStringInfoChar(&str, cp[1]); + cp++; + continue; + } + + if (i >= PG_NARGS()) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("too few parameters for format function"))); + + if (!PG_ARGISNULL(i)) + { + Oid valtype; + Datum value; + Oid typoutput; + bool typIsVarlena; + + /* 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)); + } + else + appendStringInfoString(&str, "NULL"); + i++; + } + else + appendStringInfoChar(&str, cp[0]); + } + + /* check if all arguments are used */ + if (i != PG_NARGS()) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("too many parameters"))); + + result = cstring_to_text_with_len(str.data, str.len); + pfree(str.data); + + PG_RETURN_TEXT_P(result); + } + + /* + * Non variadic text_format function - only wrapper + * Print and check format string + */ + Datum + text_format_nv(PG_FUNCTION_ARGS) + { + return text_format(fcinfo); + } *** ./src/include/catalog/pg_proc.h.orig 2010-02-26 03:01:21.000000000 +0100 --- ./src/include/catalog/pg_proc.h 2010-03-05 14:03:17.995737254 +0100 *************** *** 2712,2717 **** --- 2712,2721 ---- 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 = 3098 ( format PGNSP PGUID 12 1 0 2276 f f f f f i 2 0 25 "25 2276" "{25,2276}" "{i,v}" _null_ _null_ text_format _null_ _null_ _null_ )); + DESCR("format text message"); + DATA(insert OID = 3099 ( format PGNSP PGUID 12 1 0 0 f f f f f i 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"); *** ./src/include/utils/builtins.h.orig 2010-02-26 03:01:28.000000000 +0100 --- ./src/include/utils/builtins.h 2010-03-05 13:13:24.979862366 +0100 *************** *** 730,735 **** --- 730,738 ---- extern Datum string_agg_delim_transfn(PG_FUNCTION_ARGS); extern Datum string_agg_finalfn(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); *** ./src/test/regress/expected/text.out.orig 2007-06-07 01:00:50.000000000 +0200 --- ./src/test/regress/expected/text.out 2010-03-05 14:09:47.000000000 +0100 *************** *** 51,53 **** --- 51,76 ---- 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. + select format('Hello % %', 'World', 10); + format + ---------------- + Hello World 10 + (1 row) + + select format('users: %, date: %', 10, to_date('20080809','YYYYMMDD')); + format + ----------------------------- + users: 10, date: 08-09-2008 + (1 row) + + select format('Hello'); + format + -------- + Hello + (1 row) + + -- should to fail! + select format('Hello %'); + ERROR: too few parameters + select format('Hello',10); + ERROR: too many parameters *** ./src/test/regress/sql/text.sql.orig 2007-06-07 01:00:50.000000000 +0200 --- ./src/test/regress/sql/text.sql 2010-03-05 14:09:34.083738128 +0100 *************** *** 28,30 **** --- 28,39 ---- -- but not this: select 3 || 4.0; + + select format('Hello % %', 'World', 10); + select format('users: %, date: %', 10, to_date('20080809','YYYYMMDD')); + select format('Hello'); + + -- should to fail! + select format('Hello %'); + select format('Hello',10); +