第五章. 函數(shù)
內(nèi)容
SQL 函數(shù)
數(shù)學(xué)函數(shù)
字符串函數(shù)
日期/時(shí)間函數(shù)
格式化函數(shù)
幾何函數(shù)
IP V4 函數(shù)
描述可用的 Postgres 內(nèi)置函數(shù)．
許多數(shù)據(jù)類型可以用函數(shù)轉(zhuǎn)換成其他相關(guān)類型．另外，有一些類型相關(guān)的函數(shù)．一些函數(shù)只能通過操作符使用，文檔中也可能只有操作符描述．
SQL 函數(shù)
“SQL 函數(shù)”是由 SQL92 標(biāo)準(zhǔn)定義的構(gòu)件，它們有函數(shù)樣的語法但是不能做為簡(jiǎn)單函數(shù)來實(shí)現(xiàn)。
表 5-1. SQL 函數(shù)
函數(shù) 返回 描述 例子
COALESCE(list) 非NULL 返回列表list中第一個(gè)非NULL值 COALESCE(r"le>, c2 + 5, 0)
IFNULL(input,non-NULL substitute) 非NULL 如果第一個(gè)為NULL，返回第二個(gè)參數(shù) IFNULL(c1, 'N/A')
CASE WHEN expr THEN expr [...] ELSE expr END expr 返回第一個(gè)真子句的表達(dá)式 CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
數(shù)學(xué)函數(shù)
表 5-2. 數(shù)學(xué)函數(shù)
函數(shù) 返回 描述 例子
abs(float8) float8 絕對(duì)值 abs(-17.4)
degrees(float8) float8 弧度到角度 degrees(0.5)
exp(float8) float8 求 e 的指定指數(shù)的冪 exp(2.0)
ln(float8) float8 自然對(duì)數(shù) ln(2.0)
log(float8) float8 10 為底的對(duì)數(shù) log(2.0)
pi() float8 基本常量 pi()
pow(float8,float8) float8 對(duì)指定底數(shù)求指定指數(shù)的冪 pow(2.0, 16.0)
radians(float8) float8 角度到弧度 radians(45.0)
round(float8) float8 圓整到最接近的整數(shù) round(42.4)
sqrt(float8) float8 平方根 sqrt(2.0)
cbrt(float8) float8 立方根 cbrt(27.0)
trunc(float8) float8 截除（向零方向） trunc(42.4)
float(int) float8 把整數(shù)轉(zhuǎn)換成浮點(diǎn) float(2)
float4(int) float4 把整數(shù)轉(zhuǎn)換成浮點(diǎn) float4(2)
integer(float) int 把浮點(diǎn)轉(zhuǎn)換成整數(shù) integer(2.0)
上面列出的用于 FLOAT8 的大部分函數(shù)同樣可以用于 NUMERIC 類型。
表 5-3。超越數(shù)學(xué)函數(shù)
函數(shù) 返回 描述 例子
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)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
字符串函數(shù)
SQL92 定義了使用指明語法的字符串函數(shù)． 這類函數(shù)中有一些是使用其他 Postgres 函數(shù)實(shí)現(xiàn)的。為 SQL92 支持的字符串類型是char，varchar，和text。
表 5-4. SQL92 字符串函數(shù)
函數(shù) 返回 描述 例子
char_length(string) int4 字串長(zhǎng)度 char_length('jose')
character_length(string) int4 字串長(zhǎng)度 char_length('jose')
lower(string) string 把字串轉(zhuǎn)成小寫 lower('TOM')
octet_length(string) int4 字串的存儲(chǔ)長(zhǎng)度 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 把字串轉(zhuǎn)成大寫 upper('tom')
許多字符串函數(shù)可用于 text，varchar()，和 char() 類型。另一些在內(nèi)部使用，用于實(shí)現(xiàn)上面列出的 SQL92 字符串函數(shù)。
表 5-5. 字符串函數(shù)
函數(shù) 返回 描述 例子
char(text) char 將text轉(zhuǎn)換成char型 char('text string')
char(varchar) char 將 varchar 轉(zhuǎn)換成 char char(varchar 'varchar string')
initcap(text) text 將每個(gè)詞首字母轉(zhuǎn)成大寫 initcap('thomas')
lpad(text,int,text) text 字串左填充定長(zhǎng)指定字符 lpad('hi',4,'??')
ltrim(text,text) text 將字串左邊的指定字符刪除 ltrim('xxxxtrim','x')
textpos(text,text) text 定位指定子字串 position('high','ig')
rpad(text,int,text) text 字串右填充定長(zhǎng)指定字符 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 轉(zhuǎn)換成 text text('char string')
text(varchar) text 將 varchar 轉(zhuǎn)換成 text text(varchar 'varchar string')
translate(text,from,to) text 轉(zhuǎn)換串中指定字符 translate('12345', '1', 'a')
varchar(char) varchar 將 char 轉(zhuǎn)換成varchar varchar('char string')
varchar(text) varchar 將 text 轉(zhuǎn)換成varchar varchar('text string')
大多數(shù)顯式定義為使用text類型的函數(shù)也可用于 char() 和 varchar() 參數(shù)。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
時(shí)間/日期函數(shù)
日期/時(shí)間函數(shù)提供了一整套處理各種日期/時(shí)間類型的有效工具。
表 5-6. 日期/時(shí)間函數(shù)
函數(shù) 返回 描述 例子
abstime(timestamp) abstime 轉(zhuǎn)換成 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 時(shí)間部分 date_part('hour',interval '4 hrs 3 mins')
date_trunc(text,timestamp) timestamp 截?cái)嗳掌凇ate_trunc('month',abstime 'now')
interval(reltime) interval 轉(zhuǎn)換成 interval interval(reltime '4 hours')
isfinite(timestamp) bool 是無窮時(shí)間么？ isfinite(timestamp 'now')
isfinite(interval) bool 是無窮時(shí)間么？ isfinite(interval '4 hrs')
reltime(interval) reltime 轉(zhuǎn)換成 reltime reltime(interval '4 hrs')
timestamp(date) timestamp 轉(zhuǎn)換成 timestamp timestamp(date 'today')
timestamp(date,time) timestamp 轉(zhuǎn)換成 timestamp timestamp(timestamp '1998-02-24',time '23:07');
to_char(timestamp,text) text 轉(zhuǎn)換成 string to_char(timestamp '1998-02-24','DD');
對(duì)于 date_part 和 date_trunc 函數(shù)而言， 參數(shù)可以是 `year'， `month'， `day'， `hour'， `minute'，和 `second'，還可以是更特殊的單位`decade'， `century'， `millenium'， `millisecond'，和 `microsecond'． date_part 允許使用 `dow' 返回星期日數(shù)（date of week），用 'week'返回 ISO 定義的一年中的星期，和用 `epoch' 返回自1970（對(duì)于timestamp）以來的秒數(shù)或 'epoch' 返回總共流逝的秒數(shù) （對(duì)于 interval 而言）。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
格式化函數(shù)
作者：由 Karel Zak 寫于 2000-01-24。
Postgres 格式化函數(shù)提供一套有效的工具用于把各種數(shù)據(jù)類型（日期/時(shí)間，int，float，numeric）轉(zhuǎn)換成格式化的字符串以及反過來從格式化的字符串轉(zhuǎn)換成原始的數(shù)據(jù)類型。
注意：所有格式化函數(shù)的第二個(gè)參數(shù)是用于轉(zhuǎn)換的模板。
表 5-7. 格式化函數(shù)
函數(shù) 返回 描述 例子
to_char(timestamp, text) text 把 timestamp 轉(zhuǎn)換成 string to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text) text 把 int4/int8 轉(zhuǎn)換成 string to_char(125, '999')
to_char(float, text) text 把 float4/float8 轉(zhuǎn)換成 string to_char(125.8, '999D9')
to_char(numeric, text) text 把 numeric 轉(zhuǎn)換成 string to_char(numeric '-125.8', '999D99S')
to_date(text, text) date 把 string 轉(zhuǎn)換成 date to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text) date 把 string 轉(zhuǎn)換成 timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric 把 string 轉(zhuǎn)換成 numeric to_number('12,454.8-', '99G999D9S')
表 5-8. 用于 date/time 轉(zhuǎn)換的模板
模板 描述
HH 一天的小時(shí)數(shù) (01-12)
HH12 一天的小時(shí)數(shù) (01-12)
HH24 一天的小時(shí)數(shù) (00-23)
MI 分鐘 (00-59)
SS 秒 (00-59)
SSSS 午夜后的秒 (0-86399)
AM or A.M. or PM or P.M. 正午標(biāo)識(shí)（大寫）
am or a.m. or pm or p.m. 正午標(biāo)識(shí)（小寫）
Y,YYY 帶逗號(hào)的年（4 和更多位）
YYYY 年（4和更多位）
YYY 年的后三位
YY 年的后兩位
Y 年的最后一位
BC or B.C. or AD or A.D. 年標(biāo)識(shí)（大寫）
bc or b.c. or ad or a.d. 年標(biāo)識(shí)（小寫）
MONTH 全長(zhǎng)大寫月份名（9字符）
Month 全長(zhǎng)混合大小寫月份名（9字符）
month 全長(zhǎng)小寫月份名（9字符）
MON 大寫縮寫月份名（3字符）
Mon 縮寫混合大小寫月份名（3字符）
mon 小寫縮寫月份名（3字符）
MM 月份 (01-12)
DAY 全長(zhǎng)大寫日期名（9字符）
Day 全長(zhǎng)混合大小寫日期名（9字符）
day 全長(zhǎng)小寫日期名（9字符）
DY 縮寫大寫日期名（3字符）
Dy 縮寫混合大小寫日期名（3字符）
dy 縮寫小寫日期名（3字符）
DDD 一年里的日子(001-366)
DD 一個(gè)月里的日子(01-31)
D 一周里的日子(1-7；SUN=1)
W 一個(gè)月里的周數(shù)
WW 一年里的周數(shù)
CC 世紀(jì)（2 位）
J Julian 日期（自公元前4712年1月1日來的日期）
Q 季度
RM 羅馬數(shù)字的月份（I-XII；I=JAN）－大寫
rm 羅馬數(shù)字的月份（I-XII；I=JAN）－小寫
所有模板都都允許使用前綴和后綴修改器。模板里總是允許使用修改器。前綴 'FX' 只是一個(gè)全局修改器。
表 5-9. 用于日期/時(shí)間模板 to_char() 的后綴
后綴 描述 例子
FM 填充模式前綴 FMMonth
TH 大寫順序數(shù)后綴 DDTH
th 小寫順序數(shù)后綴 DDTH
FX 固定模式全局選項(xiàng)（見下面） FX Month DD Day
SP 拼寫模式（還未實(shí)現(xiàn)） DDSP
用法須知：
如果沒有使用 FX 選項(xiàng)，to_timestamp 和 to_date 忽略空白。FX 必須做為模板里的第一個(gè)條目聲明。
反斜杠（"/"）必須用做雙反斜杠（"http://"），例如 '//HH//MI//SS'。
雙引號(hào)（'"'）之間的字串被忽略并且不被分析。如果你想向輸出寫雙引號(hào)，你必須在雙引號(hào)前面放置一個(gè)雙反斜杠（'//'），例如 '//"YYYY Month//"'。
to_char 支持不帶前導(dǎo)雙引號(hào)（'"'）的文本，但是在雙引號(hào)之間的任何字串會(huì)被迅速處理并且還保證不會(huì)被當(dāng)作模板關(guān)鍵字解釋（例如：'"Hello Year: "YYYY'）。
表 5-10. 用于 to_char(numeric) 的模板
模板 描述
9 帶有指定位數(shù)的值
0 前導(dǎo)零的值
. （句點(diǎn)） 小數(shù)點(diǎn)
, （逗號(hào)） 分組（千）分隔符
PR 尖括號(hào)內(nèi)負(fù)值
S 帶負(fù)號(hào)的負(fù)值（使用本地化）
L 貨幣符號(hào)（使用本地化）
D 小數(shù)點(diǎn)（使用本地化）
G 分組分隔符（使用本地化）
MI 在指明的位置的負(fù)號(hào)（如果數(shù)字 < 0）
PL 在指明的位置的正號(hào)（如果數(shù)字 > 0）
SG 在指明的位置的正/負(fù)號(hào)
RN 羅馬數(shù)字（輸入在 1 和 3999 之間）
TH or th 轉(zhuǎn)換成序數(shù)
V 移動(dòng) n 位（小數(shù)）（參閱注解）
EEEE 科學(xué)記數(shù)。現(xiàn)在不支持。
用法須知：
使用 'SG'，'PL' 或 'MI' 的帶符號(hào)字并不附著在數(shù)字上面；例如，to_char(-12, 'S9999') 生成 ' -12'，而 to_char(-12, 'MI9999') 生成 '- 12'。Oracle 里的實(shí)現(xiàn)不允許在 9 前面使用 MI，而是要求 9 在 MI 前面。
PL，SG，和 TH 是 Postgres 擴(kuò)展。
9 表明一個(gè)與在 9 字串里面的一樣的數(shù)字位數(shù)。如果沒有可用的數(shù)字，那么使用一個(gè)空白（空格）。
TH 不轉(zhuǎn)換小于零的值，也不轉(zhuǎn)換小數(shù)。TH 是一個(gè) Postgres 擴(kuò)展。
V 方便地把輸入值乘以 10^n，這里 n 是跟在 V 后面的數(shù)字。to_char 不支持把 V 與一個(gè)小數(shù)點(diǎn)綁在一起使用（例如. "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'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
幾何函數(shù)
幾何類型 point，box，lseg，line，path，polygon 和 circle 擁有很多的內(nèi)置支持函數(shù)。
表 5-11. 幾何函數(shù)
函數(shù) 返回 描述 例子
area(object) float8 目標(biāo)的范圍 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 對(duì)象中心 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 是開環(huán)路徑嗎？ isopen(path '[(0,0),(1,1),(2,0)]')
length(object) float8 對(duì)象長(zhǎng)度 length(path '((-1,0),(1,0))')
pclose(path) path 把路徑轉(zhuǎn)換為閉合 popen(path '[(0,0),(1,1),(2,0)]')
npoint(path) int4 點(diǎn)數(shù) npoints(path '[(0,0),(1,1),(2,0)]')
popen(path) path 把路徑轉(zhuǎn)換為開環(huán)的 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. 幾何類型轉(zhuǎn)換函數(shù)
函數(shù) 返回 描述 例子
box(circle) box 將圓轉(zhuǎn)換成長(zhǎng)方形 box('((0,0),2.0)'::circle)
box(point,point) box 將點(diǎn)轉(zhuǎn)換成長(zhǎng)方形 box('(0,0)'::point,'(1,1)'::point)
box(polygon) box 將多邊形轉(zhuǎn)換成長(zhǎng)方形 box('((0,0),(1,1),(2,0))'::polygon)
circle(box) circle 轉(zhuǎn)換成圓 circle('((0,0),(1,1))'::box)
circle(point,float8) circle 轉(zhuǎn)換成圓 circle('(0,0)'::point,2.0)
lseg(box) lseg 將長(zhǎng)方形轉(zhuǎn)成線段 lseg('((-1,0),(1,0))'::box)
lseg(point,point) lseg 轉(zhuǎn)換成線段 lseg('(-1,0)'::point,'(1,0)'::point)
path(polygon) point 轉(zhuǎn)換成路徑 path('((0,0),(1,1),(2,0))'::polygon)
point(circle) point 轉(zhuǎn)換成點(diǎn) (中心) point('((0,0),2.0)'::circle)
point(lseg,lseg) point 轉(zhuǎn)換成點(diǎn)(相交) 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 轉(zhuǎn)換成12點(diǎn)的多邊形 polygon('((0,0),(1,1))'::box)
polygon(circle) polygon 轉(zhuǎn)換成12點(diǎn)的多邊形 polygon('((0,0),2.0)'::circle)
polygon(npts,circle) polygon 轉(zhuǎn)換成npts點(diǎn)的多邊形 polygon(12,'((0,0),2.0)'::circle)
polygon(path) polygon 轉(zhuǎn)換成多邊形 polygon('((0,0),(1,1),(2,0))'::path)
表 5-14. 幾何升級(jí)函數(shù)
函數(shù) 返回 描述 例子
isoldpath(path) path 測(cè)試是否v6.1前的路徑 isoldpath('(1,3,0,0,1,1,2,0)'::path)
revertpoly(polygon) polygon 轉(zhuǎn)換v6.1前的多邊形 revertpoly('((0,0),(1,1),(2,0))'::polygon)
upgradepath(path) path 轉(zhuǎn)換v6.1前的路徑 upgradepath('(1,3,0,0,1,1,2,0)'::path)
upgradepoly(polygon) polygon 轉(zhuǎn)換v6.1前的多邊形 upgradepoly('(0,1,2,0,1,0)'::polygon)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
IP V4 函數(shù)
表 5-15. PostgresIP V4 函數(shù)
函數(shù) 返回 描述 例子
broadcast(cidr) text 用text類型構(gòu)造廣播地址 broadcast('192.168.1.5/24')
broadcast(inet) text 用text類型構(gòu)造廣播地址 broadcast('192.168.1.5/24')
host(inet) text 將主機(jī)地址以text類型抽出 host('192.168.1.5/24')
masklen(cidr) int4 計(jì)算網(wǎng)絡(luò)掩碼長(zhǎng) masklen('192.168.1.5/24')
masklen(inet) int4 計(jì)算網(wǎng)絡(luò)掩碼長(zhǎng) masklen('192.168.1.5/24')
netmask(inet) text 用text類型構(gòu)造網(wǎng)絡(luò)掩碼 netmask('192.168.1.5/24')
--------------------------------------------------------------------------------
