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