第五章. 函數
內容
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' 返回總共流逝的秒數?。▽τ凇nterval 而言）?！?
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
格式化函數
作者：由 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 必須做為模板里的第一個條目聲明?！?
反斜杠（"/"）必須用做雙反斜杠（"http://"），例如 '//HH//MI//SS'。
雙引號（'"'）之間的字串被忽略并且不被分析。如果你想向輸出寫雙引號，你必須在雙引號前面放置一個雙反斜杠（'//'），例如 '//"YYYY Month//"'?！?
to_char 支持不帶前導雙引號（'"'）的文本，但是在雙引號之間的任何字串會被迅速處理并且還保證不會被當作模板關鍵字解釋（例如：'"Hello Year: "YYYY'）?！?
表 5-10. 用于 to_char(numeric) 的模板
模板 描述
9 帶有指定位數的值
0 前導零的值
.?。ň潼c） 小數點
,?。ǘ禾枺》纸M（千）分隔符
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')
--------------------------------------------------------------------------------
