2013-05-06 08:39:20|分类：PgSQL Develop|举报|字号订阅
Postgresql 9.3 引入的一个格式化输出函数,有点类似C的sprintf用法.
语法如下 :
format(formatstrtext[,formatarg"any"[,...]])
其中formatstr是需要格式化的字符串,包含普通字符以及格式字符.
后面的动态参数用来替换formatstr中的格式字符.
格式字符的语法如下 :
%[position][flags][width]type
其中position,flags,width都是可选项. type是必须的.
1. position 指变量位置.
Astringoftheformn$wherenistheindexoftheargumenttoprint.Index1meansthefirstargumentafterformatstr.Ifthepositionisomitted,thedefaultistousethenextargumentinsequence.
注意the next argument是指前一个已经取过的位置的下一个位置.
2. flags目前只有-,且需要与width配合使用,-表示右补齐,没有-表示左补齐.
Additionaloptionscontrollinghowtheformatspecifier'soutputisformatted.Currentlytheonlysupportedflagisaminussign(-)whichwillcausetheformatspecifier'soutputtobeleft-justified.Thishasnoeffectunlessthewidthfieldisalsospecified.
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 left aligned (as if the - flag had been specified) within a field of length abswidth).
注意如果同一个格式中宽度用到了参数,那么宽度优先消耗这个参数. 因此如果没有指定位置时,默认会消耗下一个参数. 也就是宽度消耗的参数的下一个参数.
4. type指字符串类型,目前可以使用s,I,L. 分别表示字符串,identified,和literal.
The type of format conversion to 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=#selectformat('|abc|%*2$s|',E'wabc\t','10','abc');format------------------|abc|abc|(1row)
*2$指width取第二个参数. 10
s指字符串,这里去下一个参数也就是第三个参数'ab c'
2.
digoal=#selectformat('|abc|%*s|','8',E'wabc','abc');format----------------|abc|wabc|(1row)
*指width,取下一个参数. 因为前面没有取过任何参数,所以这里是'8'
s指字符串,这里去下一个参数也就是第2个参数E'wab c'.
3.
digoal=#selectformat('|abc|%-*s|',所以这里是'8'
s指字符串,这里去下一个参数也就是第2个参数E'wab c'.
- 表示右边补齐
4.
postgres=#selectformat('|abc|%3$-*s|','4','abc');format------------|abc|10|(1row)
这里的*取的是第一个参数'4',因为前面没有取过参数,从1开始.
digoal=#selectformat('|abc|%2$s|%3$-*s|','abc');format------------------------|abc|wabc|10|(1row)
这里的*取的是第3个参数. 也就是%2$后面的一个参数.
%3$-*s这个格式中,是先取width参数,再取其他参数的.
5.
digoal=#selectformat('|abc|%2$I|%3$-*I|','abc');format--------------------------|abc|"wabc"|"10"|(1row)
I指identified,类似表名,字段名. 所以如果是包含了特殊字符则需要用双引号.
digoal=#selectformat('|abc|%2$I|%3$-*I|',E'wABc','abc');format-------------------------|abc|"wABc"|"10"|(1row)digoal=#selectformat('|abc|%2$I|%3$-*I|','abc');format-----------------------|abc|wabc|"10"|(1row)
6.
digoal=#selectformat('|abc|%2$L|%3$-*L|',E'wa\bc\t','abc');format-------------------------------|abc|'wa\x08c'|'10'|(1row)digoal=#selectformat('|abc|%2$L|%3$-*L|',E'wa\bc\t\\','abc');format---------------------------------|abc|E'wa\x08c\\'|'10'|(1row)
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')
'locations''C:\\Program Files' INSERT INTO locations VALUESE)
【参考】
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
原文链接：https://www.f2er.com/postgresql/195345.html