PostgreSQL 9.3 格式化拼接字串

標籤：

  

2013-05-06 08:39:20|  分類： PgSQL Develop|舉報|字型大小 訂閱

PostgreSQL 9.3 引入的一個格式化輸出函數, 有點類似C的sprintf用法.

文法如下 : 

format(formatstr text [, formatarg "any" [, ...] ])

其中formatstr是需要格式化的字串, 包含一般字元以及格式字元.

後面的動態參數用來替換formatstr中的格式字元.

格式字元的文法如下 : 

%[position][flags][width]type

其中position, flags, width都是可選項. type是必須的.

1. position 指變數位置.

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.

注意the next argument是指前一個已經取過的位置的下一個位置.

2. flags目前只有-, 且需要與width配合使用, -表示右補齊, 沒有-表示左補齊.

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指顯示的寬度, 當寬度大於實際字串時用空格補齊, 當寬度小於實際字串長度時不會截斷字串, 相當於width不起作用. width也可以使用3. format函數的參數值來代替直接寫在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 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).

注意如果同一個格式中寬度用到了參數, 那麼寬度優先消耗這個參數. 因此如果沒有指定位置時, 預設會消耗下一個參數. 也就是寬度消耗的參數的下一個參數.

4. type指字串類型, 目前可以使用s, I, L. 分別表示字串, identified, 和literal.

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.

[使用舉例]

1. 

digoal=# select format (‘|abc|%*2$s|‘, E‘wab c\t‘, ‘10‘, ‘ab c‘);      format      ------------------ |abc|      ab c|(1 row)

*2$指width取第二個參數. 10

s指字串, 這裡去下一個參數也就是第三個參數‘ab c‘

2.

digoal=# select format (‘|abc|%*s|‘,‘8‘, E‘wab c‘, ‘10‘, ‘ab c‘);     format     ---------------- |abc|   wab c|(1 row)

*指width, 取下一個參數. 因為前面沒有取過任何參數, 所以這裡是‘8‘

s指字串, 這裡去下一個參數也就是第2個參數E‘wab c‘.

3.

digoal=# select format (‘|abc|%-*s|‘,‘8‘, E‘wab c‘, ‘10‘, ‘ab c‘);     format     ---------------- |abc|wab c   |(1 row)

*指width, 取下一個參數. 因為前面沒有取過任何參數, 所以這裡是‘8‘

s指字串, 這裡去下一個參數也就是第2個參數E‘wab c‘.

- 表示右邊補齊

4. 

postgres=# select format (‘|abc|%3$-*s|‘,‘4‘, E‘wab c‘, ‘10‘, ‘ab c‘);   format   ------------ |abc|10  |(1 row)

這裡的*取的是第一個參數‘4‘, 因為前面沒有取過參數, 從1開始.

digoal=# select format (‘|abc|%2$s|%3$-*s|‘,‘4‘, E‘wab c‘, ‘10‘, ‘ab c‘);         format         ------------------------ |abc|wab c|10        |(1 row)

這裡的*取的是第3個參數. 也就是%2$後面的一個參數.

%3$-*s這個格式中, 是先取width參數, 再取其他參數的.

5. 

digoal=# select format (‘|abc|%2$I|%3$-*I|‘,‘4‘, E‘wab c‘, ‘10‘, ‘ab c‘);          format          -------------------------- |abc|"wab c"|"10"      |(1 row)

I指identified, 類似表名, 欄位名. 所以如果是包含了特殊字元則需要用雙引號.

digoal=# select format (‘|abc|%2$I|%3$-*I|‘,‘4‘, E‘wABc‘, ‘10‘, ‘ab c‘);         format          ------------------------- |abc|"wABc"|"10"      |(1 row)digoal=# select format (‘|abc|%2$I|%3$-*I|‘,‘4‘, E‘wabc‘, ‘10‘, ‘ab c‘);        format         ----------------------- |abc|wabc|"10"      |(1 row)

6. 

digoal=# select format (‘|abc|%2$L|%3$-*L|‘,‘4‘, E‘wa\bc\t‘, ‘10‘, ‘ab c‘);            format             ------------------------------- |abc|‘wa\x08c   ‘|‘10‘      |(1 row)digoal=# select format (‘|abc|%2$L|%3$-*L|‘,‘4‘, E‘wa\bc\t\\‘, ‘10‘, ‘ab c‘);             format              --------------------------------- |abc|E‘wa\x08c  \\‘|‘10‘      |(1 row)

L指literal, 類似字串類型的值, 所以涉及逃逸. 如山.

7.

因此format可用於構造動態SQL.

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‘)

【參考】

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 格式化拼接字串