PostgreSQL7.0手册-用户手册-5. 函数

第五章.　函数
内容　
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([leadingtrailingboth]　[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')　　

--------------------------------------------------------------------------------