Hello
I reworked a implementation of format function. This respects last discussion:
* support a positional placeholders - syntax %99$x,
* support a tags: %s, I, L,
* enhanced documentation,
* enhanced reggress tests
Regards
Pavel Stehule
*** ./doc/src/sgml/func.sgml.orig 2010-10-21 14:40:44.000000000 +0200
--- ./doc/src/sgml/func.sgml 2010-10-22 10:24:18.764199680 +0200
***************
*** 1272,1277 ****
--- 1272,1280 ----
<primary>encode</primary>
</indexterm>
<indexterm>
+ <primary>format</primary>
+ </indexterm>
+ <indexterm>
<primary>initcap</primary>
</indexterm>
<indexterm>
***************
*** 1497,1502 ****
--- 1500,1524 ----
</row>
<row>
+ <entry>
+ <literal><function>format</function>(<parameter>formatstr</parameter> <type>text</type>
+ [, <parameter>str</parameter> <type>"any"</type> [, ...] ])</literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>
+ This functions can be used to create a formated string or message. There are allowed
+ three types of tags: %s as string, %I as SQL identifiers and %L as SQL literals. Attention:
+ result for %I and %L must not be same as result of <function>quote_ident</function> and
+ <function>quote_literal</function> functions, because this function doesn't try to coerce
+ parameters to <type>text</type> type and directly use a type's output functions. The placeholder
+ can be related to some explicit parameter with using a optional n$ specification inside format.
+ See also <xref linkend="plpgsql-quote-literal-example">.
+ </entry>
+ <entry><literal>format('Hello %s, %1$s', 'World')</literal></entry>
+ <entry><literal>Hello World, World</literal></entry>
+ </row>
+
+ <row>
<entry><literal><function>initcap(<parameter>string</parameter>)</function></literal></entry>
<entry><type>text</type></entry>
<entry>
*** ./doc/src/sgml/plpgsql.sgml.orig 2010-10-11 08:33:15.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2010-10-22 10:37:06.041200330 +0200
***************
*** 1250,1255 ****
--- 1250,1273 ----
<emphasis>must</> use <function>quote_literal</>,
<function>quote_nullable</>, or <function>quote_ident</>, as appropriate.
</para>
+
+ <para>
+ Building a string with dynamic SQL statement can be simplified
+ with using a <function>format</function> function (see <xref
+ linkend="functions-string">):
+ <programlisting>
+ EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
+ </programlisting>
+ A use the <function>format</function> format can be together with
+ <literal>USING</literal> clause:
+ <programlisting>
+ EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
+ USING newvalue, keyvalue;
+ </programlisting>
+ This form is little bit more efective, because a parameters
+ <literal>newvalue</literal> and <literal>keyvalue</literal> must not be
+ converted to text.
+ </para>
</example>
<para>
*** ./src/backend/utils/adt/varlena.c.orig 2010-10-11 08:33:15.000000000 +0200
--- ./src/backend/utils/adt/varlena.c 2010-10-22 09:46:18.863075117 +0200
***************
*** 3702,3704 ****
--- 3702,3939 ----
PG_RETURN_TEXT_P(result);
}
+
+ typedef struct {
+ char field_type;
+ int32 position;
+ bool is_positional;
+ } placeholder_desc;
+
+ #define INVALID_PARAMETER_ERROR(d) ereport(ERROR, \
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE), \
+ errmsg("invalid formatting string for function format"), \
+ errdetail(d)))
+
+ static const char *
+ parse_format(placeholder_desc *pd, const char *start_ptr,
+ const char *end_ptr, int nparams)
+ {
+ bool is_valid = false;
+
+ pd->is_positional = false;
+
+ while (start_ptr <= end_ptr)
+ {
+ switch (*start_ptr)
+ {
+ case 's':
+ pd->field_type = 's';
+ is_valid = true;
+ break;
+
+ case 'I':
+ pd->field_type = 'I';
+ is_valid = true;
+ break;
+
+ case 'L':
+ pd->field_type = 'L';
+ is_valid = true;
+ break;
+
+ case '0': case '1': case '2': case '3': case '4':
+ case '5': case '6': case '7': case '8': case '9':
+ if (pd->is_positional)
+ INVALID_PARAMETER_ERROR("positional parameter is defined already");
+
+ pd->position = *start_ptr - '0';
+ while (start_ptr < end_ptr && isdigit(start_ptr[1]))
+ {
+ pd->position = pd->position * 10 + *++start_ptr - '0';
+ /* don't allow to integer overflow */
+ if (pd->position >= nparams)
+ INVALID_PARAMETER_ERROR("placeholder is out of range");
+ }
+
+ /* recheck position, or first check for one digit positions */
+ if (pd->position >= nparams)
+ INVALID_PARAMETER_ERROR("placeholder is out of range");
+
+ /* next character must be a $ */
+ if (start_ptr == end_ptr && isdigit(*start_ptr))
+ INVALID_PARAMETER_ERROR("unexpected end, missing '$'");
+
+ if (*++start_ptr != '$')
+ INVALID_PARAMETER_ERROR("expected '$'");
+ start_ptr++;
+
+ pd->is_positional = true;
+ break;
+
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid formatting string for function format"),
+ errdetail("unsupported tag \"%%%.*s\"", pg_mblen(start_ptr), start_ptr)));
+ }
+
+ if (is_valid)
+ break;
+ }
+
+ if (!is_valid)
+ INVALID_PARAMETER_ERROR("missing field type");
+
+ return start_ptr;
+ }
+
+ /*
+ * Returns a formated string
+ */
+ Datum
+ text_format(PG_FUNCTION_ARGS)
+ {
+ text *fmt;
+ StringInfoData str;
+ const char *cp;
+ int i = 1;
+ size_t len;
+ const char *start_ptr;
+ const char *end_ptr;
+ text *result;
+ bool only_ordered_parameters = true;
+
+ /* 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] == '%')
+ {
+ placeholder_desc pd = { ' ', 0, false }; /* be compiler quiet */
+ Oid valtype;
+ Datum value;
+ bool isNull;
+ int arg_num;
+ Oid typoutput;
+ bool typIsVarlena;
+
+ /* initial check */
+ if (cp == end_ptr)
+ INVALID_PARAMETER_ERROR("missing field type");
+
+ /* replace escaped symbol */
+ if (cp[1] == '%')
+ {
+ appendStringInfoChar(&str, '%');
+ cp++;
+ continue;
+ }
+
+ cp = parse_format(&pd, ++cp, end_ptr, PG_NARGS());
+
+ if (pd.is_positional)
+ {
+ arg_num = pd.position;
+ only_ordered_parameters = false;
+ }
+ else
+ {
+ /* ordered arguments are not checked yet */
+ if (i >= PG_NARGS())
+ INVALID_PARAMETER_ERROR("too few parameters");
+ arg_num = i++;
+ }
+
+ value = PG_GETARG_DATUM(arg_num);
+ isNull = PG_ARGISNULL(arg_num);
+ valtype = get_fn_expr_argtype(fcinfo->flinfo, arg_num);
+ getTypeOutputInfo(valtype, &typoutput, &typIsVarlena);
+
+ switch (pd.field_type)
+ {
+ case 's':
+ {
+ if (!isNull)
+ {
+ char *outstr = OidOutputFunctionCall(typoutput, value);
+
+ appendStringInfoString(&str, outstr);
+ pfree(outstr);
+ }
+
+ break;
+ }
+
+ case 'I':
+ {
+ char *target_value;
+
+ if (isNull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("SQL identifier is undefined"),
+ errhint("You try to use NULL for %%I format")));
+
+ /* show it as sql identifier */
+ target_value = OidOutputFunctionCall(typoutput, value);
+ appendStringInfoString(&str, quote_identifier(target_value));
+ pfree(target_value);
+
+ break;
+ }
+
+ case 'L':
+ {
+ text *txt;
+ text *quoted_txt;
+
+ if (!isNull)
+ {
+ /* 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));
+
+ pfree(quoted_txt);
+ pfree(txt);
+ }
+ else
+ appendStringInfoString(&str, "NULL");
+
+ break;
+ }
+
+ default:
+ /* not possible */
+ elog(ERROR, "unexpected field type");
+ }
+ }
+ else
+ appendStringInfoChar(&str, cp[0]);
+ }
+
+ result = cstring_to_text_with_len(str.data, str.len);
+ pfree(str.data);
+
+ PG_RETURN_TEXT_P(result);
+ }
+
+ /*
+ * text_format_nv - nonvariadic wrapper for text_format function.
+ *
+ * note: this wrapper is necessary to be sanity_checks test ok
+ */
+ Datum
+ text_format_nv(PG_FUNCTION_ARGS)
+ {
+ return text_format(fcinfo);
+ }
*** ./src/include/catalog/pg_proc.h.orig 2010-10-11 08:33:15.000000000 +0200
--- ./src/include/catalog/pg_proc.h 2010-10-22 09:17:51.410199979 +0200
***************
*** 2741,2746 ****
--- 2741,2750 ----
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 = 3063 ( 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 = 3064 ( 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");
*** ./src/include/utils/builtins.h.orig 2010-10-11 08:33:15.000000000 +0200
--- ./src/include/utils/builtins.h 2010-10-22 08:49:28.642077134 +0200
***************
*** 742,747 ****
--- 742,749 ----
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);
*** ./src/test/regress/expected/text.out.orig 2010-10-11 08:33:15.000000000 +0200
--- ./src/test/regress/expected/text.out 2010-10-22 10:07:47.000000000 +0200
***************
*** 118,120 ****
--- 118,231 ----
5 | ahoj | ahoj
(11 rows)
+ select format(NULL);
+ format
+ --------
+
+ (1 row)
+
+ select format('Hello');
+ format
+ --------
+ Hello
+ (1 row)
+
+ select format('Hello %s', 'World');
+ format
+ -------------
+ Hello World
+ (1 row)
+
+ select format('Hello %%');
+ format
+ ---------
+ Hello %
+ (1 row)
+
+ select format('Hello %%%%');
+ format
+ ----------
+ Hello %%
+ (1 row)
+
+ -- should fail
+ select format('Hello %s %s', 'World');
+ ERROR: invalid formatting string for function format
+ DETAIL: too few parameters
+ select format('Hello %s');
+ ERROR: invalid formatting string for function format
+ DETAIL: too few parameters
+ select format('Hello %x', 20);
+ ERROR: invalid formatting string for function format
+ DETAIL: unsupported tag "%x"
+ -- check literal and sql identifiers
+ select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, 'Hello');
+ format
+ ----------------------------------------
+ INSERT INTO mytab VALUES('10','Hello')
+ (1 row)
+
+ select format('%s%s%s','Hello', NULL,'World');
+ format
+ ------------
+ HelloWorld
+ (1 row)
+
+ select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, NULL);
+ format
+ -------------------------------------
+ INSERT INTO mytab VALUES('10',NULL)
+ (1 row)
+
+ select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', NULL, 'Hello');
+ format
+ ----------------------------------------
+ INSERT INTO mytab VALUES(NULL,'Hello')
+ (1 row)
+
+ -- should fail, sql identifier cannot be NULL
+ select format('INSERT INTO %I VALUES(%L,%L)', NULL, 10, 'Hello');
+ ERROR: SQL identifier is undefined
+ HINT: You try to use NULL for %I format
+ -- check positional placeholders
+ select format('%1$s %3$s', 1, 2, 3);
+ format
+ --------
+ 1 3
+ (1 row)
+
+ select format('%1$s %12$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+ format
+ --------
+ 1 12
+ (1 row)
+
+ -- should fail
+ select format('%1$s %4$s', 1, 2, 3);
+ ERROR: invalid formatting string for function format
+ DETAIL: placeholder is out of range
+ select format('%1$s %13$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+ ERROR: invalid formatting string for function format
+ DETAIL: placeholder is out of range
+ select format('%1s', 1);
+ ERROR: invalid formatting string for function format
+ DETAIL: expected '$'
+ select format('%1$', 1);
+ ERROR: invalid formatting string for function format
+ DETAIL: missing field type
+ select format('%1$1', 1);
+ ERROR: invalid formatting string for function format
+ DETAIL: positional parameter is defined already
+ --checkk mix of positional and ordered placeholders
+ select format('Hello %s %1$s %s', 'World', 'Hello again');
+ format
+ -------------------------------
+ Hello World World Hello again
+ (1 row)
+
+ select format('Hello %s %s, %2$s %2$s', 'World', 'Hello again');
+ format
+ --------------------------------------------------
+ Hello World Hello again, Hello again Hello again
+ (1 row)
+
*** ./src/test/regress/sql/text.sql.orig 2010-10-11 08:33:15.000000000 +0200
--- ./src/test/regress/sql/text.sql 2010-10-22 10:41:36.369200039 +0200
***************
*** 41,43 ****
--- 41,72 ----
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;
+
+ select format(NULL);
+ select format('Hello');
+ select format('Hello %s', 'World');
+ select format('Hello %%');
+ select format('Hello %%%%');
+ -- should fail
+ select format('Hello %s %s', 'World');
+ select format('Hello %s');
+ select format('Hello %x', 20);
+ -- check literal and sql identifiers
+ select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, 'Hello');
+ select format('%s%s%s','Hello', NULL,'World');
+ select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, NULL);
+ select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', NULL, 'Hello');
+ -- should fail, sql identifier cannot be NULL
+ select format('INSERT INTO %I VALUES(%L,%L)', NULL, 10, 'Hello');
+ -- check positional placeholders
+ select format('%1$s %3$s', 1, 2, 3);
+ select format('%1$s %12$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+ -- should fail
+ select format('%1$s %4$s', 1, 2, 3);
+ select format('%1$s %13$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+ select format('%1s', 1);
+ select format('%1$', 1);
+ select format('%1$1', 1);
+ --checkk mix of positional and ordered placeholders
+ select format('Hello %s %1$s %s', 'World', 'Hello again');
+ select format('Hello %s %s, %2$s %2$s', 'World', 'Hello again');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
