第五章. 函数
内容
SQL 函数
数学函数
字符串函数
日期/时间函数
格式化函数
几何函数
IP V4 函数
描述可用的 Postgres 内置函数．
许多数据类型可以用函数转换成其他相关类型．另外，有一些类型相关的函数．一些函数只能通过操作符使用，文档中也可能只有操作符描述．
SQL 函数
“SQL 函数”是由 SQL92 标准定义的构件，它们有函数样的语法但是不能做为简单函数来实现。
表 5-1. SQL 函数
函数 返回 描述 例子
COALESCE(list) 非NULL 返回列表list中第一个非NULL值 COALESCE(r"le>, c2 + 5, 0)
IFNULL(input,non-NULL substitute) 非NULL 如果第一个为NULL，返回第二个参数 IFNULL(c1, 'N/A')
CASE WHEN expr THEN expr [...] ELSE expr END expr 返回第一个真子句的表达式 CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
数学函数
表 5-2. 数学函数
函数 返回 描述 例子
abs(float8) float8 绝对值 abs(-17.4)
degrees(float8) float8 弧度到角度 degrees(0.5)
exp(float8) float8 求 e 的指定指数的幂 exp(2.0)
ln(float8) float8 自然对数 ln(2.0)
log(float8) float8 10 为底的对数 log(2.0)
pi() float8 基本常量 pi()
pow(float8,float8) float8 对指定底数求指定指数的幂 pow(2.0, 16.0)
radians(float8) float8 角度到弧度 radians(45.0)
round(float8) float8 圆整到最接近的整数 round(42.4)
sqrt(float8) float8 平方根 sqrt(2.0)
cbrt(float8) float8 立方根 cbrt(27.0)
trunc(float8) float8 截除（向零方向） trunc(42.4)
float(int) float8 把整数转换成浮点 float(2)
float4(int) float4 把整数转换成浮点 float4(2)
integer(float) int 把浮点转换成整数 integer(2.0)
上面列出的用于 FLOAT8 的大部分函数同样可以用于 NUMERIC 类型。
表 5-3。超越数学函数
函数 返回 描述 例子
acos(float8) float8 反余弦 acos(10.0)
asin(float8) float8 反正弦 asin(10.0)
atan(float8) float8 反正切 atan(10.0)
atan2(float8,float8) float8 反余切 atan3(10.0,20.0)
cos(float8) float8 余弦 cos(0.4)
cot(float8) float8 余切 cot(20.0)
sin(float8) float8 正弦 cos(0.4)
tan(float8) float8 正切 tan(0.4)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
字符串函数
SQL92 定义了使用指明语法的字符串函数． 这类函数中有一些是使用其他 Postgres 函数实现的。为 SQL92 支持的字符串类型是char，varchar，和text。
表 5-4. SQL92 字符串函数
函数 返回 描述 例子
char_length(string) int4 字串长度 char_length('jose')
character_length(string) int4 字串长度 char_length('jose')
lower(string) string 把字串转成小写 lower('TOM')
octet_length(string) int4 字串的存储长度 octet_length('jose')
position(string in string) int4 定位声明子串的位置 position('o' in 'Tom')
substring(string [from int] [for int]) string 抽取指明的子串 substring('Tom' from 2 for 2)
trim([leading|trailing|both] [string] from string) string 从字串中裁掉字符 trim(both 'x' from 'xTomx')
upper(text) text 把字串转成大写 upper('tom')
许多字符串函数可用于 text，varchar()，和 char() 类型。另一些在内部使用，用于实现上面列出的 SQL92 字符串函数。
表 5-5. 字符串函数
函数 返回 描述 例子
char(text) char 将text转换成char型 char('text string')
char(varchar) char 将 varchar 转换成 char char(varchar 'varchar string')
initcap(text) text 将每个词首字母转成大写 initcap('thomas')
lpad(text,int,text) text 字串左填充定长指定字符 lpad('hi',4,'??')
ltrim(text,text) text 将字串左边的指定字符删除 ltrim('xxxxtrim','x')
textpos(text,text) text 定位指定子字串 position('high','ig')
rpad(text,int,text) text 字串右填充定长指定字符 rpad('hi',4,'x')
rtrim(text,text) text 将字串右边的指定字符删除 rtrim('trimxxxx','x')
substr(text,int[,int]) text 抽取指定位置子字符 substr('hi there',3,5)
text(char) text 将 char 转换成 text text('char string')
text(varchar) text 将 varchar 转换成 text text(varchar 'varchar string')
translate(text,from,to) text 转换串中指定字符 translate('12345', '1', 'a')
varchar(char) varchar 将 char 转换成varchar varchar('char string')
varchar(text) varchar 将 text 转换成varchar varchar('text string')
大多数显式定义为使用text类型的函数也可用于 char() 和 varchar() 参数。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
时间/日期函数
日期/时间函数提供了一整套处理各种日期/时间类型的有效工具。
表 5-6. 日期/时间函数
函数 返回 描述 例子
abstime(timestamp) abstime 转换成 abstime abstime(timestamp 'now')
age(timestamp) interval 保存月和年 age(timestamp '1957-06-13')
age(timestamp,timestamp) interval 保存月和年 age('now', timestamp '1957-06-13')
date_part(text,timestamp) float8 日期部分 date_part('dow',timestamp 'now')
date_part(text,interval) float8 时间部分 date_part('hour',interval '4 hrs 3 mins')
date_trunc(text,timestamp) timestamp 截断日期 date_trunc('month',abstime 'now')
interval(reltime) interval 转换成 interval interval(reltime '4 hours')
isfinite(timestamp) bool 是无穷时间么？ isfinite(timestamp 'now')
isfinite(interval) bool 是无穷时间么？ isfinite(interval '4 hrs')
reltime(interval) reltime 转换成 reltime reltime(interval '4 hrs')
timestamp(date) timestamp 转换成 timestamp timestamp(date 'today')
timestamp(date,time) timestamp 转换成 timestamp timestamp(timestamp '1998-02-24',time '23:07');
to_char(timestamp,text) text 转换成 string to_char(timestamp '1998-02-24','DD');
对于 date_part 和 date_trunc 函数而言， 参数可以是 `year'， `month'， `day'， `hour'， `minute'，和 `second'，还可以是更特殊的单位`decade'， `century'， `millenium'， `millisecond'，和 `microsecond'． date_part 允许使用 `dow' 返回星期日数（date of week），用 'week'返回 ISO 定义的一年中的星期，和用 `epoch' 返回自1970（对于timestamp）以来的秒数或 'epoch' 返回总共流逝的秒数 （对于 interval 而言）。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
格式化函数
作者：由 Karel Zak 写于 2000-01-24。
Postgres 格式化函数提供一套有效的工具用于把各种数据类型（日期/时间，int，float，numeric）转换成格式化的字符串以及反过来从格式化的字符串转换成原始的数据类型。
注意：所有格式化函数的第二个参数是用于转换的模板。
表 5-7. 格式化函数
函数 返回 描述 例子
to_char(timestamp, text) text 把 timestamp 转换成 string to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text) text 把 int4/int8 转换成 string to_char(125, '999')
to_char(float, text) text 把 float4/float8 转换成 string to_char(125.8, '999D9')
to_char(numeric, text) text 把 numeric 转换成 string to_char(numeric '-125.8', '999D99S')
to_date(text, text) date 把 string 转换成 date to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text) date 把 string 转换成 timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric 把 string 转换成 numeric to_number('12,454.8-', '99G999D9S')
表 5-8. 用于 date/time 转换的模板
模板 描述
HH 一天的小时数 (01-12)
HH12 一天的小时数 (01-12)
HH24 一天的小时数 (00-23)
MI 分钟 (00-59)
SS 秒 (00-59)
SSSS 午夜后的秒 (0-86399)
AM or A.M. or PM or P.M. 正午标识（大写）
am or a.m. or pm or p.m. 正午标识（小写）
Y,YYY 带逗号的年（4 和更多位）
YYYY 年（4和更多位）
YYY 年的后三位
YY 年的后两位
Y 年的最后一位
BC or B.C. or AD or A.D. 年标识（大写）
bc or b.c. or ad or a.d. 年标识（小写）
MONTH 全长大写月份名（9字符）
Month 全长混合大小写月份名（9字符）
month 全长小写月份名（9字符）
MON 大写缩写月份名（3字符）
Mon 缩写混合大小写月份名（3字符）
mon 小写缩写月份名（3字符）
MM 月份 (01-12)
DAY 全长大写日期名（9字符）
Day 全长混合大小写日期名（9字符）
day 全长小写日期名（9字符）
DY 缩写大写日期名（3字符）
Dy 缩写混合大小写日期名（3字符）
dy 缩写小写日期名（3字符）
DDD 一年里的日子(001-366)
DD 一个月里的日子(01-31)
D 一周里的日子(1-7；SUN=1)
W 一个月里的周数
WW 一年里的周数
CC 世纪（2 位）
J Julian 日期（自公元前4712年1月1日来的日期）
Q 季度
RM 罗马数字的月份（I-XII；I=JAN）－大写
rm 罗马数字的月份（I-XII；I=JAN）－小写
所有模板都都允许使用前缀和后缀修改器。模板里总是允许使用修改器。前缀 'FX' 只是一个全局修改器。
表 5-9. 用于日期/时间模板 to_char() 的后缀
后缀 描述 例子
FM 填充模式前缀 FMMonth
TH 大写顺序数后缀 DDTH
th 小写顺序数后缀 DDTH
FX 固定模式全局选项（见下面） FX Month DD Day
SP 拼写模式（还未实现） DDSP
用法须知：
如果没有使用 FX 选项，to_timestamp 和 to_date 忽略空白。FX 必须做为模板里的第一个条目声明。
反斜杠（"\"）必须用做双反斜杠（"\\"），例如 '\\HH\\MI\\SS'。
双引号（'"'）之间的字串被忽略并且不被分析。如果你想向输出写双引号，你必须在双引号前面放置一个双反斜杠（'\\'），例如 '\\"YYYY Month\\"'。
to_char 支持不带前导双引号（'"'）的文本，但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释（例如：'"Hello Year: "YYYY'）。
表 5-10. 用于 to_char(numeric) 的模板
模板 描述
9 带有指定位数的值
0 前导零的值
. （句点） 小数点
, （逗号） 分组（千）分隔符
PR 尖括号内负值
S 带负号的负值（使用本地化）
L 货币符号（使用本地化）
D 小数点（使用本地化）
G 分组分隔符（使用本地化）
MI 在指明的位置的负号（如果数字 < 0）
PL 在指明的位置的正号（如果数字 > 0）
SG 在指明的位置的正/负号
RN 罗马数字（输入在 1 和 3999 之间）
TH or th 转换成序数
V 移动 n 位（小数）（参阅注解）
EEEE 科学记数。现在不支持。
用法须知：
使用 'SG'，'PL' 或 'MI' 的带符号字并不附着在数字上面；例如，to_char(-12, 'S9999') 生成 ' -12'，而 to_char(-12, 'MI9999') 生成 '- 12'。Oracle 里的实现不允许在 9 前面使用 MI，而是要求 9 在 MI 前面。
PL，SG，和 TH 是 Postgres 扩展。
9 表明一个与在 9 字串里面的一样的数字位数。如果没有可用的数字，那么使用一个空白（空格）。
TH 不转换小于零的值，也不转换小数。TH 是一个 Postgres 扩展。
V 方便地把输入值乘以 10^n，这里 n 是跟在 V 后面的数字。to_char 不支持把 V 与一个小数点绑在一起使用（例如. "99.9V99" 是不允许的）。
表 5-11. to_char 例子
输入 输出
to_char(now(),'Day, HH12:MI:SS') 'Tuesday , 05:39:18'
to_char(now(),'FMDay, HH12:MI:SS') 'Tuesday, 05:39:18'
to_char(-0.1,'99.99') ' -.10'
to_char(-0.1,'FM9.99') '-.1'
to_char(0.1,'0.9') ' 0.1'
to_char(12,'9990999.9') ' 0012.0'
to_char(12,'FM9990999.9') '0012'
to_char(485,'999') ' 485'
to_char(-485,'999') '-485'
to_char(485,'9 9 9') ' 4 8 5'
to_char(1485,'9,999') ' 1,485'
to_char(1485,'9G999') ' 1 485'
to_char(148.5,'999.999') ' 148.500'
to_char(148.5,'999D999') ' 148,500'
to_char(3148.5,'9G999D999') ' 3 148,500'
to_char(-485,'999S') '485-'
to_char(-485,'999MI') '485-'
to_char(485,'999MI') '485'
to_char(485,'PL999') '+485'
to_char(485,'SG999') '+485'
to_char(-485,'SG999') '-485'
to_char(-485,'9SG99') '4-85'
to_char(-485,'999PR') '<485>'
to_char(485,'L999') 'DM 485
to_char(485,'RN') ' CDLXXXV'
to_char(485,'FMRN') 'CDLXXXV'
to_char(5.2,'FMRN') V
to_char(482,'999th') ' 482nd'
to_char(485, '"Good number:"999') 'Good number: 485'
to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999') 'Pre-decimal: 485 Post-decimal: .800'
to_char(12,'99V999') ' 12000'
to_char(12.4,'99V999') ' 12400'
to_char(12.45, '99V9') ' 125'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
几何函数
几何类型 point，box，lseg，line，path，polygon 和 circle 拥有很多的内置支持函数。
表 5-11. 几何函数
函数 返回 描述 例子
area(object) float8 目标的范围 area(box '((0,0),(1,1))')
box(box,box) box 方的交 box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')
center(object) point 对象中心 center(box '((0,0),(1,2))')
diameter(circle) float8 圆直径 diameter(circle '((0,0),2.0)')
height(box) float8 方的竖直高度 height(box '((0,0),(1,1))')
isclosed(path) bool 是闭合路径吗？ isclosed(path '((0,0),(1,1),(2,0))')
isopen(path) bool 是开环路径吗？ isopen(path '[(0,0),(1,1),(2,0)]')
length(object) float8 对象长度 length(path '((-1,0),(1,0))')
pclose(path) path 把路径转换为闭合 popen(path '[(0,0),(1,1),(2,0)]')
npoint(path) int4 点数 npoints(path '[(0,0),(1,1),(2,0)]')
popen(path) path 把路径转换为开环的 popen(path '((0,0),(1,1),(2,0))')
radius(circle) float8 圆半径 radius(circle '((0,0),2.0)')
width(box) float8 水平尺寸（宽） width(box '((0,0),(1,1))')
表 5-13. 几何类型转换函数
函数 返回 描述 例子
box(circle) box 将圆转换成长方形 box('((0,0),2.0)'::circle)
box(point,point) box 将点转换成长方形 box('(0,0)'::point,'(1,1)'::point)
box(polygon) box 将多边形转换成长方形 box('((0,0),(1,1),(2,0))'::polygon)
circle(box) circle 转换成圆 circle('((0,0),(1,1))'::box)
circle(point,float8) circle 转换成圆 circle('(0,0)'::point,2.0)
lseg(box) lseg 将长方形转成线段 lseg('((-1,0),(1,0))'::box)
lseg(point,point) lseg 转换成线段 lseg('(-1,0)'::point,'(1,0)'::point)
path(polygon) point 转换成路径 path('((0,0),(1,1),(2,0))'::polygon)
point(circle) point 转换成点 (中心) point('((0,0),2.0)'::circle)
point(lseg,lseg) point 转换成点(相交) point('((-1,0),(1,0))'::lseg,'((-2,-2),(2,2))'::lseg)
point(polygon) point 多边形中心 point('((0,0),(1,1),(2,0))'::polygon)
polygon(box) polygon 转换成12点的多边形 polygon('((0,0),(1,1))'::box)
polygon(circle) polygon 转换成12点的多边形 polygon('((0,0),2.0)'::circle)
polygon(npts,circle) polygon 转换成npts点的多边形 polygon(12,'((0,0),2.0)'::circle)
polygon(path) polygon 转换成多边形 polygon('((0,0),(1,1),(2,0))'::path)
表 5-14. 几何升级函数
函数 返回 描述 例子
isoldpath(path) path 测试是否v6.1前的路径 isoldpath('(1,3,0,0,1,1,2,0)'::path)
revertpoly(polygon) polygon 转换v6.1前的多边形 revertpoly('((0,0),(1,1),(2,0))'::polygon)
upgradepath(path) path 转换v6.1前的路径 upgradepath('(1,3,0,0,1,1,2,0)'::path)
upgradepoly(polygon) polygon 转换v6.1前的多边形 upgradepoly('(0,1,2,0,1,0)'::polygon)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
IP V4 函数
表 5-15. PostgresIP V4 函数
函数 返回 描述 例子
broadcast(cidr) text 用text类型构造广播地址 broadcast('192.168.1.5/24')
broadcast(inet) text 用text类型构造广播地址 broadcast('192.168.1.5/24')
host(inet) text 将主机地址以text类型抽出 host('192.168.1.5/24')
masklen(cidr) int4 计算网络掩码长 masklen('192.168.1.5/24')
masklen(inet) int4 计算网络掩码长 masklen('192.168.1.5/24')
netmask(inet) text 用text类型构造网络掩码 netmask('192.168.1.5/24')
--------------------------------------------------------------------------------