PostgreSQL 9.3 Format Stitching string

2013-05-06 08:39:20  | Category: pgsql Develop| report | Font Size Subscribe

PostgreSQL 9.3 Introduces a formatted output function that is somewhat similar to the sprintf usage of C.

The syntax is as follows:

Format (formatstr text [, Formatarg "any" [, ...])

Where Formatstr is a string that needs to be formatted, containing ordinary characters and format characters.

The following dynamic parameters are used to replace the format characters in the FORMATSTR.

The syntax for format characters is as follows:

%[position][flags][width]type

Where position, flags, width are optional. The type is required.

1. Position refers to the position of the variable.

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 was to use the next argument in sequence.

Note that the next argument refers to the next position of the previous position that has been taken.

2. The flags are currently only-and need to be used in conjunction with width-to indicate right-padded, not-to-be-left-padded.

Additional options Controlling how the format specifier's output is formatted.  Currently the only supported flag are a minus sign (-) which would cause the format specifier ' s output to be left-justified. This have no effect unless the width field is also specified.

Width refers to the displayed widths, which are padded with blanks when the width is greater than the actual string, and do not truncate the string when the width is less than the actual string length, which is equivalent to width not working. Width can also be used with 3. The parameter value of the Format function is substituted for the direct write in Formatstr.

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 in the-flag) with spaces as needed to fill the width. A too-small width does not cause truncation of the output, and is simply ignored. The width may is 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, which argument is consumed before the argument that's used for the format Specifier 's Value. IfThe width argument isNegative,The result isLeft aligned( as if the-flag had been specified)within a field of length abs(width).

Note that if the width of the same format is used for parameters, then the width takes precedence over this parameter. Therefore, if you do not specify a location, the next parameter is consumed by default. That is, the next parameter of the width-consuming parameter.

4. Type refers to string types and can now use S, I, L, respectively, to represent strings, identified, and literal.

 the type of format conversion to  Use To produce the format specifier ' s output. The following types is 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 a error for the value of IS 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 percent may is used to output a literal% character.

[Example of Use]

1.

digoal=# Select Format (' |abc|%*2$s| ', E ' wab c\t ', ' ten ', ' ab C ');      Format------------------|abc| AB c| (1 row)

*2$ refers to width to take the second parameter. 10

s refers to a string, where the next argument is the third argument ' AB C '

2.

digoal=# Select Format (' |abc|%*s| ', ' 8 ', E ' wab C ', ' Ten ', ' ab C ');   Format----------------|abc| WAB c| (1 row)

* Refer to width and remove a parameter. Because no parameters have been taken before, so here is ' 8 '

s refers to a string, where the next parameter is the 2nd parameter, E ' wab C '.

3.

digoal=# Select Format (' |abc|%-*s| ', ' 8 ', E ' wab C ', ' Ten ', ' ab C '); Format----------------|abc|wab C | (1 row)

* Refer to width and remove a parameter. Because no parameters have been taken before, so here is ' 8 '

s refers to a string, where the next parameter is the 2nd parameter, E ' WAB C '.

-Indicates right-hand completion

4.

postgres=# Select Format (' |abc|%3$-*s| ', ' 4 ', E ' wab C ', ' Ten ', ' ab C '); Format------------|abc|10 | (1 row)

Here the * takes the first parameter ' 4 ', since the previous parameter has not been taken, starting from 1.

digoal=# Select Format (' |abc|%2$s|%3$-*s| ', ' 4 ', E ' wab C ', ' Ten ', ' ab C '); Format------------------------|abc|wab c|10 | (1 row)

Here the * takes the 3rd parameter. That is, a parameter behind%2$.

%3$-*s in this format, the width parameter is taken first, and then the other parameters are taken.

5.

digoal=# Select Format (' |abc|%2$i|%3$-*i| ', ' 4 ', E ' wab C ', ' Ten ', ' ab C '); Format--------------------------|abc| " WAB C "|" 10 "| (1 row)

I means identified, similar to table name, field name. So if you include special characters, you need to use double quotes.

digoal=# Select Format (' |abc|%2$i|%3$-*i| ', ' 4 ', E ' wABc ', ' ten ', ' ab C '); Format-------------------------|abc| " WABc "|" 10 "|        (1 row) digoal=# Select Format (' |abc|%2$i|%3$-*i| ', ' 4 ', E ' WABC ', ' ten ', ' ab C '); Format-----------------------|abc|wabc| " 10 "| (1 row)

6.

digoal=# Select Format (' |abc|%2$l|%3$-*l| ', ' 4 ', E ' wa\bc\t ', ' ten ', ' ab C '); Format-------------------------------|abc| ' wa\x08c ' | ' 10 ' |             (1 row) digoal=# Select Format (' |abc|%2$l|%3$-*l| ', ' 4 ', E ' wa\bc\t\\ ', ' ten ', ' ab C '); Format---------------------------------|abc| E ' wa\x08c \ \ ' | 10 ' | (1 row)

L refers to literal, similar to the value of a string type, so involves escaping. such as mountains.

7.

Therefore, format can be used to construct dynamic SQL.

 select format   "INSERT into%I VALUES (%l) '     E  Span style= "Color:rgb (0, 136, 0); > ' o\ ' Reilly '  
Result : INSERT into "Foo Bar" VALUES (' O ' ' Reilly ')

 select format   "INSERT into%I VALUES (%l) '     E  Span style= "Color:rgb (0, 136, 0); > ' c:\\program Files '    
Result : INSERT into Locations VALUES (E' c:\\program Files ')

Reference

1. http://www.postgresql.org/docs/devel/static/functions-string.html

2. Http://www.postgresql.org/docs/devel/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

3. Man 3 sprintf

PostgreSQL 9.3 Format Stitching string