PostgreSQL教程(七)：函數(shù)和操作符詳解（3）
更新時(shí)間：2015年05月04日 10:06:58 投稿：junjie
這篇文章主要介紹了PostgreSQL教程(七)：函數(shù)和操作符詳解（3）,本文講解了序列操作函數(shù)、條件表達(dá)式、數(shù)組函數(shù)和操作符、系統(tǒng)信息函數(shù)、系統(tǒng)管理函數(shù)等內(nèi)容,需要的朋友可以參考下
九、序列操作函數(shù)：
序列對(duì)象(也叫序列生成器)都是用CREATE SEQUENCE創(chuàng)建的特殊的單行表。一個(gè)序列對(duì)象通常用于為行或者表生成唯一的標(biāo)識(shí)符。下面序列函數(shù)，為我們從序列對(duì)象中獲取最新的序列值提供了簡(jiǎn)單和并發(fā)讀取安全的方法。
| 函數(shù) | 返回類型 | 描述 |
| nextval(regclass) | bigint | 遞增序列對(duì)象到它的下一個(gè)數(shù)值并且返回該值。這個(gè)動(dòng)作是自動(dòng)完成的。即使多個(gè)會(huì)話并發(fā)運(yùn)行nextval，每個(gè)進(jìn)程也會(huì)安全地收到一個(gè)唯一的序列值。 |
| currval(regclass) | bigint | 在當(dāng)前會(huì)話中返回最近一次nextval抓到的該序列的數(shù)值。(如果在本會(huì)話中從未在該序列上調(diào)用過 nextval，那么會(huì)報(bào)告一個(gè)錯(cuò)誤。)請(qǐng)注意因?yàn)榇撕瘮?shù)返回一個(gè)會(huì)話范圍的數(shù)值，而且也能給出一個(gè)可預(yù)計(jì)的結(jié)果，因此可以用于判斷其它會(huì)話是否執(zhí)行過nextval。 |
| lastval() | bigint | 返回當(dāng)前會(huì)話里最近一次nextval返回的數(shù)值。這個(gè)函數(shù)等效于currval，只是它不用序列名為參數(shù)，它抓取當(dāng)前會(huì)話里面最近一次nextval使用的序列。如果當(dāng)前會(huì)話還沒有調(diào)用過nextval，那么調(diào)用lastval將會(huì)報(bào)錯(cuò)。 |
| setval(regclass, bigint) | bigint | 重置序列對(duì)象的計(jì)數(shù)器數(shù)值。設(shè)置序列的last_value字段為指定數(shù)值并且將其is_called字段設(shè)置為true，表示下一次nextval將在返回?cái)?shù)值之前遞增該序列。 |
| setval(regclass, bigint, boolean) | bigint | 重置序列對(duì)象的計(jì)數(shù)器數(shù)值。功能等同于上面的setval函數(shù)，只是is_called可以設(shè)置為true或false。如果將其設(shè)置為false，那么下一次nextval將返回該數(shù)值，隨后的nextval才開始遞增該序列。 |
對(duì)于regclass參數(shù)，僅需用單引號(hào)括住序列名即可，因此它看上去就像文本常量。為了達(dá)到和處理普通SQL對(duì)象一樣的兼容性，這個(gè)字串將被轉(zhuǎn)換成小寫，除非該序列名是用雙引號(hào)括起，如：
復(fù)制代碼 代碼如下:
nextval('foo') --操作序列號(hào)foo
nextval('FOO') --操作序列號(hào)foo
nextval('"Foo"') --操作序列號(hào)Foo
SELECT setval('foo', 42); --下次nextval將返回43
SELECT setval('foo', 42, true);
SELECT setval('foo', 42, false); --下次nextval將返回42
十、條件表達(dá)式：
1. CASE：
SQL CASE表達(dá)式是一種通用的條件表達(dá)式，類似于其它語言中的if/else語句。
復(fù)制代碼 代碼如下:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
condition是一個(gè)返回boolean的表達(dá)式。如果為真，那么CASE表達(dá)式的結(jié)果就是符合條件的result。如果結(jié)果為假，那么以相同方式搜尋隨后的WHEN子句。如果沒有WHEN condition為真，那么case表達(dá)式的結(jié)果就是在ELSE子句里的值。如果省略了ELSE子句而且沒有匹配的條件，結(jié)果為NULL，如：
復(fù)制代碼 代碼如下:
MyTest=> SELECT * FROM testtable;
i
---
1
2
3
(3 rows)
MyTest=> SELECT i, CASE WHEN i=1 THEN 'one'
MyTest-> WHEN i=2 THEN 'two'
MyTest-> ELSE 'other'
MyTest-> END
MyTest-> FROM testtable;
i | case
---+-------
1 | one
2 | two
3 | other
(3 rows)
注：CASE表達(dá)式并不計(jì)算任何對(duì)于判斷結(jié)果并不需要的子表達(dá)式。
2. COALESCE：
COALESCE返回它的第一個(gè)非NULL的參數(shù)的值。它常用于在為顯示目的檢索數(shù)據(jù)時(shí)用缺省值替換NULL值。
復(fù)制代碼 代碼如下:
COALESCE(value[, ...])
和CASE表達(dá)式一樣，COALESCE將不會(huì)計(jì)算不需要用來判斷結(jié)果的參數(shù)。也就是說，在第一個(gè)非空參數(shù)右邊的參數(shù)不會(huì)被計(jì)算。
3. NULLIF：
當(dāng)且僅當(dāng)value1和value2相等時(shí)，NULLIF才返回NULL。否則它返回value1。
復(fù)制代碼 代碼如下:
NULLIF(value1, value2)
MyTest=> SELECT NULLIF('abc','abc');
nullif
--------
(1 row)
MyTest=> SELECT NULLIF('abcd','abc');
nullif
--------
abcd
(1 row)
4. GREATEST和LEAST：
GREATEST和LEAST函數(shù)從一個(gè)任意的數(shù)字表達(dá)式列表里選取最大或者最小的數(shù)值。列表中的NULL數(shù)值將被忽略。只有所有表達(dá)式的結(jié)果都是NULL的時(shí)候，結(jié)果才會(huì)是NULL。
復(fù)制代碼 代碼如下:
GREATEST(value [, ...])
LEAST(value [, ...])
MyTest=> SELECT GREATEST(1,3,5);
greatest
----------
5
(1 row)
MyTest=> SELECT LEAST(1,3,5,NULL);
least
-------
1
(1 row)
十一、數(shù)組函數(shù)和操作符：
1. PostgreSQL中提供的用于數(shù)組的操作符列表：
| 操作符 | 描述 | 例子 | 結(jié)果 |
| = | 等于 | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t |
| <> | 不等于 | ARRAY[1,2,3] <> ARRAY[1,2,4] | t |
| < | 小于 | ARRAY[1,2,3] < ARRAY[1,2,4] | t |
| > | 大于 | ARRAY[1,4,3] > ARRAY[1,2,4] | t |
| <= | 小于或等于 | ARRAY[1,2,3] <= ARRAY[1,2,3] | t |
| >= | 大于或等于 | ARRAY[1,4,3] >= ARRAY[1,4,3] | t |
| || | 數(shù)組與數(shù)組連接 | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
| || | 數(shù)組與數(shù)組連接 | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
| || | 元素與數(shù)組連接 | 3 || ARRAY[4,5,6] | {3,4,5,6} |
| || | 元素與數(shù)組連接 | ARRAY[4,5,6] || 7 | {4,5,6,7} |
2. PostgreSQL中提供的用于數(shù)組的函數(shù)列表：
| 函數(shù) | 返回類型 | 描述 | 例子 | 結(jié)果 |
| array_cat(anyarray, anyarray) | anyarray | 連接兩個(gè)數(shù)組 | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
| array_append(anyarray, anyelement) | anyarray | 向一個(gè)數(shù)組末尾附加一個(gè)元素 | array_append(ARRAY[1,2], 3) | {1,2,3} |
| array_prepend(anyelement, anyarray) | anyarray | 向一個(gè)數(shù)組開頭附加一個(gè)元素 | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
| array_dims(anyarray) | text | 返回一個(gè)數(shù)組維數(shù)的文本表示 | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
| array_lower(anyarray, int) | int | 返回指定的數(shù)組維數(shù)的下界 | array_lower(array_prepend(0, ARRAY[1,2,3]), 1) | 0 |
| array_upper(anyarray, int) | int | 返回指定數(shù)組維數(shù)的上界 | array_upper(ARRAY[1,2,3,4], 1) | 4 |
| array_to_string(anyarray, text) | text | 使用提供的分隔符連接數(shù)組元素 | array_to_string(ARRAY[1, 2, 3], '~^~') | 1~^~2~^~3 |
| string_to_array(text, text) | text[] | 使用指定的分隔符把字串拆分成數(shù)組元素 | string_to_array('xx~^~yy~^~zz', '~^~') | {xx,yy,zz} |
十二、系統(tǒng)信息函數(shù)：
1. PostgreSQL中提供的和數(shù)據(jù)庫相關(guān)的函數(shù)列表：
| 名字 | 返回類型 | 描述 |
| current_database() | name | 當(dāng)前數(shù)據(jù)庫的名字 |
| current_schema() | name | 當(dāng)前模式的名字 |
| current_schemas(boolean) | name[] | 在搜索路徑中的模式名字 |
| current_user | name | 目前執(zhí)行環(huán)境下的用戶名 |
| inet_client_addr() | inet | 連接的遠(yuǎn)端地址 |
| inet_client_port() | int | 連接的遠(yuǎn)端端口 |
| inet_server_addr() | inet | 連接的本地地址 |
| inet_server_port() | int | 連接的本地端口 |
| session_user | name | 會(huì)話用戶名 |
| pg_postmaster_start_time() | timestamp | postmaster啟動(dòng)的時(shí)間 |
| user | name | current_user |
| version() | text | PostgreSQL版本信息 |
2. 允許用戶在程序里查詢對(duì)象訪問權(quán)限的函數(shù)：
| 名字 | 描述 | 可用權(quán)限 |
| has_table_privilege(user,table,privilege) | 用戶是否有訪問表的權(quán)限 | SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER |
| has_table_privilege(table,privilege) | 當(dāng)前用戶是否有訪問表的權(quán)限 | SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER |
| has_database_privilege(user,database,privilege) | 用戶是否有訪問數(shù)據(jù)庫的權(quán)限 | CREATE/TEMPORARY |
| has_database_privilege(database,privilege) | 當(dāng)前用戶是否有訪問數(shù)據(jù)庫的權(quán)限 | CREATE/TEMPORARY |
| has_function_privilege(user,function,privilege) | 用戶是否有訪問函數(shù)的權(quán)限 | EXECUTE |
| has_function_privilege(function,privilege) | 當(dāng)前用戶是否有訪問函數(shù)的權(quán)限 | EXECUTE |
| has_language_privilege(user,language,privilege) | 用戶是否有訪問語言的權(quán)限 | USAGE |
| has_language_privilege(language,privilege) | 當(dāng)前用戶是否有訪問語言的權(quán)限 | USAGE |
| has_schema_privilege(user,schema,privilege) | 用戶是否有訪問模式的權(quán)限 | CREAT/USAGE |
| has_schema_privilege(schema,privilege) | 當(dāng)前用戶是否有訪問模式的權(quán)限 | CREAT/USAGE |
| has_tablespace_privilege(user,tablespace,privilege) | 用戶是否有訪問表空間的權(quán)限 | CREATE |
| has_tablespace_privilege(tablespace,privilege) | 當(dāng)前用戶是否有訪問表空間的權(quán)限 | CREATE |
注：以上函數(shù)均返回boolean類型。要評(píng)估一個(gè)用戶是否在權(quán)限上持有賦權(quán)選項(xiàng)，給權(quán)限鍵字附加 WITH GRANT OPTION；比如 'UPDATE WITH GRANT OPTION'。
3. 模式可視性查詢函數(shù)：
那些判斷一個(gè)對(duì)象是否在當(dāng)前模式搜索路徑中可見的函數(shù)。 如果一個(gè)表所在的模式在搜索路徑中，并且沒有同名的表出現(xiàn)在搜索路徑的更早的地方，那么就說這個(gè)表視可見的。 它等效于表可以不帶明確模式修飾進(jìn)行引用。
| 名字 | 描述 | 應(yīng)用類型 |
| pg_table_is_visible(table_oid) | 該表/視圖是否在搜索路徑中可見 | regclass |
| pg_type_is_visible(type_oid) | 該類/視圖型是否在搜索路徑中可見 | regtype |
| pg_function_is_visible(function_oid) | 該函數(shù)是否在搜索路徑中可見 | regprocedure |
| pg_operator_is_visible(operator_oid) | 該操作符是否在搜索路徑中可見 | regoperator |
| pg_opclass_is_visible(opclass_oid) | 該操作符表是否在搜索路徑中可見 | regclass |
| pg_conversion_is_visible(conversion_oid) | 轉(zhuǎn)換是否在搜索路徑中可見 | regoperator |
注：以上函數(shù)均返回boolean類型，所有這些函數(shù)都需要對(duì)象 OID 標(biāo)識(shí)作為檢查的對(duì)象。
復(fù)制代碼 代碼如下:
postgres=# SELECT pg_table_is_visible('testtable'::regclass);
pg_table_is_visible
---------------------
t
(1 row)
4. 系統(tǒng)表信息函數(shù)：
| 名字 | 返回類型 | 描述 |
| format_type(type_oid,typemod) | text | 獲取一個(gè)數(shù)據(jù)類型的SQL名稱 |
| pg_get_viewdef(view_oid) | text | 為視圖獲取CREATE VIEW命令 |
| pg_get_viewdef(view_oid,pretty_bool) | text | 為視圖獲取CREATE VIEW命令 |
| pg_get_ruledef(rule_oid) | text | 為規(guī)則獲取CREATE RULE命令 |
| pg_get_ruledef(rule_oid,pretty_bool) | text | 為規(guī)則獲取CREATE RULE命令 |
| pg_get_indexdef(index_oid) | text | 為索引獲取CREATE INDEX命令 |
| pg_get_indexdef(index_oid,column_no,pretty_bool) | text | 為索引獲取CREATE INDEX命令， 如果column_no不為零，則是只獲取一個(gè)索引字段的定義 |
| pg_get_triggerdef(trigger_oid) | text | 為觸發(fā)器獲取CREATE [CONSTRAINT] TRIGGER |
| pg_get_constraintdef(constraint_oid) | text | 獲取一個(gè)約束的定義 |
| pg_get_constraintdef(constraint_oid,pretty_bool) | text | 獲取一個(gè)約束的定義 |
| pg_get_expr(expr_text,relation_oid) | text | 反編譯一個(gè)表達(dá)式的內(nèi)部形式，假設(shè)其中的任何Vars都引用第二個(gè)參數(shù)指出的關(guān)系 |
| pg_get_expr(expr_text,relation_oid, pretty_bool) | text | 反編譯一個(gè)表達(dá)式的內(nèi)部形式，假設(shè)其中的任何Vars都引用第二個(gè)參數(shù)指出的關(guān)系 |
| pg_get_userbyid(roleid) | name | 獲取給出的ID的角色名 |
| pg_get_serial_sequence(table_name,column_name) | text | 獲取一個(gè)serial或者bigserial字段使用的序列名字 |
| pg_tablespace_databases(tablespace_oid) | setof oid | 獲取在指定表空間(OID表示)中擁有對(duì)象的一套數(shù)據(jù)庫的OID的集合 |
這些函數(shù)大多數(shù)都有兩個(gè)變種，其中一個(gè)可以選擇對(duì)結(jié)果的"漂亮的打印"。 漂亮打印的格式更容易讀，但是缺省的格式更有可能被將來的PostgreSQL版本用同樣的方法解釋；如果是用于轉(zhuǎn)儲(chǔ)，那么盡可能避免使用漂亮打印。 給漂亮打印參數(shù)傳遞false生成的結(jié)果和那個(gè)沒有這個(gè)參數(shù)的變種生成的結(jié)果完全一樣。
十三、系統(tǒng)管理函數(shù)：
1. 查詢以及修改運(yùn)行時(shí)配置參數(shù)的函數(shù)：
| 名字 | 返回類型 | 描述 |
| current_setting(setting_name) | text | 當(dāng)前設(shè)置的值 |
| set_config(setting_name,new_value,is_local) | text | 設(shè)置參數(shù)并返回新值 |
current_setting用于以查詢形式獲取setting_name設(shè)置的當(dāng)前數(shù)值。它和SQL命令SHOW是等效的。 比如：
復(fù)制代碼 代碼如下:
MyTest=# SELECT current_setting('datestyle');
current_setting
-----------------
ISO, YMD
(1 row)
set_config將參數(shù)setting_name設(shè)置為new_value。如果is_local設(shè)置為true，那么新數(shù)值將只應(yīng)用于當(dāng)前事務(wù)。如果你希望新的數(shù)值應(yīng)用于當(dāng)前會(huì)話，那么應(yīng)該使用false。它等效于SQL命令SET。比如：
復(fù)制代碼 代碼如下:
MyTest=# SELECT set_config('log_statement_stats','off', false);
set_config
------------
off
(1 row)
2. 數(shù)據(jù)庫對(duì)象尺寸函數(shù)：
| 名字 | 返回類型 | 描述 |
| pg_tablespace_size(oid) | bigint | 指定OID代表的表空間使用的磁盤空間 |
| pg_tablespace_size(name) | bigint | 指定名字的表空間使用的磁盤空間 |
| pg_database_size(oid) | bigint | 指定OID代表的數(shù)據(jù)庫使用的磁盤空間 |
| pg_database_size(name) | bigint | 指定名稱的數(shù)據(jù)庫使用的磁盤空間 |
| pg_relation_size(oid) | bigint | 指定OID代表的表或者索引所使用的磁盤空間 |
| pg_relation_size(text) | bigint | 指定名稱的表或者索引使用的磁盤空間。這個(gè)名字可以用模式名修飾 |
| pg_total_relation_size(oid) | bigint | 指定OID代表的表使用的磁盤空間，包括索引和壓縮數(shù)據(jù) |
| pg_total_relation_size(text) | bigint | 指定名字的表所使用的全部磁盤空間，包括索引和壓縮數(shù)據(jù)。表名字可以用模式名修飾。 |
| pg_size_pretty(bigint) | text | 把字節(jié)計(jì)算的尺寸轉(zhuǎn)換成一個(gè)人類易讀的尺寸單位 |
3. 數(shù)據(jù)庫對(duì)象位置函數(shù)：
| 名字 | 返回類型 | 描述 |
| pg_relation_filenode(relationregclass) | oid | 獲取指定對(duì)象的文件節(jié)點(diǎn)編號(hào)(通常為對(duì)象的oid值)。 |
| pg_relation_filepath(relationregclass) | text | 獲取指定對(duì)象的完整路徑名。 |
復(fù)制代碼 代碼如下:
mydatabase=# select pg_relation_filenode('testtable');
pg_relation_filenode
----------------------
17877
(1 row)
mydatabase=# select pg_relation_filepath('testtable');
pg_relation_filepath
----------------------------------------------
pg_tblspc/17633/PG_9.1_201105231/17636/17877
(1 row)
該博客中提供的所有信息均源自PostgreSQL官方文檔，編寫該篇博客的主要目的是便于今后的查閱，特此聲明。
相關(guān)文章
這篇文章主要介紹了PostgreSQL教程(十)：性能提升技巧,本文講解了使用EXPLAIN、批量數(shù)據(jù)插入、關(guān)閉自動(dòng)提交、使用COPY、 刪除索引、刪除外鍵約束等技巧,需要的朋友可以參考下2015-05-05
PostgreSQL索引掃描時(shí)為什么index only scan不返回ctid
這篇文章主要介紹了PostgreSQL索引掃描時(shí)為什么index only scan不返回ctid的原因探索，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-09-09
聊聊PostgreSql table和磁盤文件的映射關(guān)系
這篇文章主要介紹了聊聊PostgreSql table和磁盤文件的映射關(guān)系，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
如何將excel表格數(shù)據(jù)導(dǎo)入postgresql數(shù)據(jù)庫
這篇文章主要介紹了如何將excel表格數(shù)據(jù)導(dǎo)入postgresql數(shù)據(jù)庫,本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
Navicat連接postgresql時(shí)出現(xiàn)'datlastsysoid?does?not?exist&
這篇文章主要給大家介紹了關(guān)于Navicat連接postgresql時(shí)出現(xiàn)'datlastsysoid?does?not?exist'報(bào)錯(cuò)問題的完美解決辦法,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-02-02
這篇文章主要介紹了PostgreSQL中的VACUUM命令用法說明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02
postgresql 補(bǔ)齊空值、自定義查詢字段并賦值操作
這篇文章主要介紹了postgresql 補(bǔ)齊空值、自定義查詢字段并賦值操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql rank() over, dense_rank(), row_number()用法區(qū)別
這篇文章主要介紹了postgresql rank() over, dense_rank(), row_number()的用法區(qū)別，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL教程(十七)：客戶端命令（1）,本文講解了createdb、dropdb、reindexdb、vacuumdb、createuser、dropuser等命令,需要的朋友可以參考下2015-05-05
PostgreSQL通過oracle_fdw訪問Oracle數(shù)據(jù)的實(shí)現(xiàn)步驟
通過類似于Oracle數(shù)據(jù)庫DBLINK的方式去實(shí)現(xiàn)PostgreSQL訪問oracle數(shù)據(jù)庫，本地搭建測(cè)試環(huán)境并配置相關(guān)配置，接下來通過本文給大家分享PostgreSQL通過oracle_fdw訪問Oracle數(shù)據(jù)的實(shí)現(xiàn)步驟，感興趣的朋友一起看看吧2021-05-05