From 73e7025bd8eed941a068f0a7a71e02dca8d38d1c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 14 Mar 2013 22:56:56 -0400 Subject: Extend format() to handle field width and left/right alignment. This change adds some more standard sprintf() functionality to format(). Pavel Stehule, reviewed by Dean Rasheed and Kyotaro Horiguchi --- doc/src/sgml/func.sgml | 222 ++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 211 insertions(+), 11 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 372e2b65751..896c08c09cf 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1519,21 +1519,13 @@ format format(formatstr text - [, str "any" [, ...] ]) + [, formatarg "any" [, ...] ]) text Format arguments according to a format string. - This function is similar to the C function - sprintf, but only the following conversion specifications - are recognized: %s interpolates the corresponding - argument as a string; %I escapes its argument as - an SQL identifier; %L escapes its argument as an - SQL literal; %% outputs a literal %. - A conversion can reference an explicit parameter position by preceding - the conversion specifier with n$, where - n is the argument position. - See also . + This function is similar to the C function sprintf. + See . format('Hello %s, %1$s', 'World') Hello World, World @@ -2847,6 +2839,214 @@ + + <function>format</function> + + + format + + + + The function format produces output formatted according to + a format string, in a style similar to the C function + sprintf. + + + + +format(formatstr text [, formatarg "any" [, ...] ]) + + formatstr is a format string that specifies how the + result should be formatted. Text in the format string is copied + directly to the result, except where format specifiers are + used. Format specifiers act as placeholders in the string, defining how + subsequent function arguments should be formatted and inserted into the + result. Each formatarg argument is converted to text + according to the usual output rules for its data type, and then formatted + and inserted into the result string according to the format specifier(s). + + + + Format specifiers are introduced by a % character and have + the form + +%[position][flags][width]type + + where the component fields are: + + + + position (optional) + + + A string of the form n$ where + n is the index of the argument to print. + Index 1 means the first argument after + formatstr. If the position is + omitted, the default is to use the next argument in sequence. + + + + + + flags (optional) + + + Additional options controlling how the format specifier's output is + formatted. Currently the only supported flag is a minus sign + (-) which will cause the format specifier's output to be + left-justified. This has no effect unless the width + field is also specified. + + + + + + width (optional) + + + Specifies the minimum number of characters to use to + display the format specifier's output. The output is padded on the + left or right (depending on the - flag) with spaces as + needed to fill the width. A too-small width does not cause + truncation of the output, but is simply ignored. The width may be + specified using any of the following: a positive integer; an + asterisk (*) to use the next function argument as the + width; or a string of the form *n$ to + use the nth function argument as the width. + + + + If the width comes from a function argument, that argument is + consumed before the argument that is used for the format specifier's + value. If the width argument is negative, the result is left + aligned (as if the - flag had been specified) within a + field of length abs(width). + + + + + + type (required) + + + The type of format conversion to use to produce the format + specifier's output. The following types are supported: + + + + s formats the argument value as a simple + string. A null value is treated as an empty string. + + + + + I treats the argument value as an SQL + identifier, double-quoting it if necessary. + It is an error for the value to be null. + + + + + L quotes the argument value as an SQL literal. + A null value is displayed as the string NULL, without + quotes. + + + + + + + + + + + In addition to the format specifiers described above, the special sequence + %% may be used to output a literal % character. + + + + Here are some examples of the basic format conversions: + + +SELECT format('Hello %s', 'World'); +Result: Hello World + +SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three'); +Result: Testing one, two, three, % + +SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly'); +Result: INSERT INTO "Foo bar" VALUES('O''Reilly') + +SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files'); +Result: INSERT INTO locations VALUES(E'C:\\Program Files') + + + + + Here are examples using width fields + and the - flag: + + +SELECT format('|%10s|', 'foo'); +Result: | foo| + +SELECT format('|%-10s|', 'foo'); +Result: |foo | + +SELECT format('|%*s|', 10, 'foo'); +Result: | foo| + +SELECT format('|%*s|', -10, 'foo'); +Result: |foo | + +SELECT format('|%-*s|', 10, 'foo'); +Result: |foo | + +SELECT format('|%-*s|', -10, 'foo'); +Result: |foo | + + + + + These examples show use of position fields: + + +SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three'); +Result: Testing three, two, one + +SELECT format('|%*2$s|', 'foo', 10, 'bar'); +Result: | bar| + +SELECT format('|%1$*2$s|', 'foo', 10, 'bar'); +Result: | foo| + + + + + Unlike the standard C function sprintf, + PostgreSQL's format function allows format + specifiers with and without position fields to be mixed + in the same format string. A format specifier without a + position field always uses the next argument after the + last argument consumed. + In addition, the format function does not require all + function arguments to be used in the format string. + For example: + + +SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); +Result: Testing three, two, three + + + + + The %I and %L format specifiers are particularly + useful for safely constructing dynamic SQL statements. See + . + + + -- cgit v1.2.3