PostgreSQL學習手冊(函式和操作符<三>)

九、序列操作函式：

    序列物件(也叫序列生成器)都是用CREATE SEQUENCE建立的特殊的單行表。一個序列物件通常用於為行或者表生成唯一的識別符號。下面序列函式，為我們從序列物件中獲取最新的序列值提供了簡單和併發讀取安全的方法。

函式	返回型別	描述
nextval(regclass)	bigint	遞增序列物件到它的下一個數值並且返回該值。這個動作是自動完成的。即使多個會話併發執行nextval，每個程式也會安全地收到一個唯一的序列值。
currval(regclass)	bigint	在當前會話中返回最近一次nextval抓到的該序列的數值。(如果在本會話中從未在該序列上呼叫過 nextval，那麼會報告一個錯誤。)請注意因為此函式返回一個會話範圍的數值，而且也能給出一個可預計的結果，因此可以用於判斷其它會話是否執行過nextval。
lastval()	bigint	返回當前會話裡最近一次nextval返回的數值。這個函式等效於currval，只是它不用序列名為引數，它抓取當前會話裡面最近一次nextval使用的序列。如果當前會話還沒有呼叫過nextval，那麼呼叫lastval將會報錯。
setval(regclass, bigint)	bigint	重置序列物件的計數器數值。設定序列的last_value欄位為指定數值並且將其is_called欄位設定為true，表示下一次nextval將在返回數值之前遞增該序列。
setval(regclass, bigint, boolean)	bigint	重置序列物件的計數器數值。功能等同於上面的setval函式，只是is_called可以設定為true或false。如果將其設定為false，那麼下一次nextval將返回該數值，隨後的nextval才開始遞增該序列。

    對於regclass引數，僅需用單引號括住序列名即可，因此它看上去就像文字常量。為了達到和處理普通SQL物件一樣的相容性，這個字串將被轉換成小寫，除非該序列名是用雙引號括起，如：
    nextval('foo')     --操作序列號foo
    nextval('FOO')    --操作序列號foo
    nextval('"Foo"')   --操作序列號Foo
    SELECT setval('foo', 42);    --下次nextval將返回43
    SELECT setval('foo', 42, true);   
    SELECT setval('foo', 42, false);   --下次nextval將返回42

   
十、條件表示式：

    1. CASE：
    SQL CASE表示式是一種通用的條件表示式，類似於其它語言中的if/else語句。
    CASE WHEN condition THEN result
        [WHEN ...]
        [ELSE result]
    END
    condition是一個返回boolean的表示式。如果為真，那麼CASE表示式的結果就是符合條件的result。如果結果為假，那麼以相同方式 搜尋隨後的WHEN子句。如果沒有WHEN condition為真，那麼case表示式的結果就是在ELSE子句裡的值。如果省略了ELSE子句而且沒有匹配的條件，結果為NULL，如：
    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表示式並不計算任何對於判斷結果並不需要的子表示式。
    
    2. COALESCE：
    COALESCE返回它的第一個非NULL的引數的值。它常用於在為顯示目的檢索資料時用預設值替換NULL值。
    COALESCE(value[, ...])
    和CASE表示式一樣，COALESCE將不會計算不需要用來判斷結果的引數。也就是說，在第一個非空引數右邊的引數不會被計算。
    
    3. NULLIF：
    當且僅當value1和value2相等時，NULLIF才返回NULL。否則它返回value1。
    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函式從一個任意的數字表示式列表裡選取最大或者最小的數值。列表中的NULL數值將被忽略。只有所有表示式的結果都是NULL的時候，結果才會是NULL。
    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)
    
十一、陣列函式和操作符：

    1. PostgreSQL中提供的用於陣列的操作符列表：

操作符	描述	例子	結果
=	等於	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
||	陣列與陣列連線	ARRAY[1,2,3] || ARRAY[4,5,6]	{1,2,3,4,5,6}
||	陣列與陣列連線	ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]	{{1,2,3},{4,5,6},{7,8,9}}
||	元素與陣列連線	3 || ARRAY[4,5,6]	{3,4,5,6}
||	元素與陣列連線	ARRAY[4,5,6] || 7	{4,5,6,7}

    2. PostgreSQL中提供的用於陣列的函式列表：

函式	返回型別	描述	例子	結果
array_cat(anyarray, anyarray)	anyarray	連線兩個陣列	array_cat(ARRAY[1,2,3], ARRAY[4,5])	{1,2,3,4,5}
array_append(anyarray, anyelement)	anyarray	向一個陣列末尾附加一個元素	array_append(ARRAY[1,2], 3)	{1,2,3}
array_prepend(anyelement, anyarray)	anyarray	向一個陣列開頭附加一個元素	array_prepend(1, ARRAY[2,3])	{1,2,3}
array_dims(anyarray)	text	返回一個陣列維數的文字表示	array_dims(ARRAY[[1,2,3], [4,5,6]])	[1:2][1:3]
array_lower(anyarray, int)	int	返回指定的陣列維數的下界	array_lower(array_prepend(0, ARRAY[1,2,3]), 1)	0
array_upper(anyarray, int)	int	返回指定陣列維數的上界	array_upper(ARRAY[1,2,3,4], 1)	4
array_to_string(anyarray, text)	text	使用提供的分隔符連線陣列元素	array_to_string(ARRAY[1, 2, 3], '~^~')	1~^~2~^~3
string_to_array(text, text)	text[]	使用指定的分隔符把字串拆分成陣列元素	string_to_array('xx~^~yy~^~zz', '~^~')	{xx,yy,zz}

十二、系統資訊函式：

    1. PostgreSQL中提供的和資料庫相關的函式列表：

名字	返回型別	描述
current_database()	name	當前資料庫的名字
current_schema()	name	當前模式的名字
current_schemas(boolean)	name[]	在搜尋路徑中的模式名字
current_user	name	目前執行環境下的使用者名稱
inet_client_addr()	inet	連線的遠端地址
inet_client_port()	int	連線的遠端埠
inet_server_addr()	inet	連線的本地地址
inet_server_port()	int	連線的本地埠
session_user	name	會話使用者名稱
pg_postmaster_start_time()	timestamp	postmaster啟動的時間
user	name	current_user
version()	text	PostgreSQL版本資訊

    2. 允許使用者在程式裡查詢物件訪問許可權的函式：

名字	描述	可用許可權
has_table_privilege(user,table,privilege)	使用者是否有訪問表的許可權	SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER
has_table_privilege(table,privilege)	當前使用者是否有訪問表的許可權	SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER
has_database_privilege(user,database,privilege)	使用者是否有訪問資料庫的許可權	CREATE/TEMPORARY
has_database_privilege(database,privilege)	當前使用者是否有訪問資料庫的許可權	CREATE/TEMPORARY
has_function_privilege(user,function,privilege)	使用者是否有訪問函式的許可權	EXECUTE
has_function_privilege(function,privilege)	當前使用者是否有訪問函式的許可權	EXECUTE
has_language_privilege(user,language,privilege)	使用者是否有訪問語言的許可權	USAGE
has_language_privilege(language,privilege)	當前使用者是否有訪問語言的許可權	USAGE
has_schema_privilege(user,schema,privilege)	使用者是否有訪問模式的許可權	CREAT/USAGE
has_schema_privilege(schema,privilege)	當前使用者是否有訪問模式的許可權	CREAT/USAGE
has_tablespace_privilege(user,tablespace,privilege)	使用者是否有訪問表空間的許可權	CREATE
has_tablespace_privilege(tablespace,privilege)	當前使用者是否有訪問表空間的許可權	CREATE

    注：以上函式均返回boolean型別。要評估一個使用者是否在許可權上持有賦權選項，給許可權鍵字附加 WITH GRANT OPTION；比如 'UPDATE WITH GRANT OPTION'。
    3. 模式可視性查詢函式：
    那些判斷一個物件是否在當前模式搜尋路徑中可見的函式。 如果一個表所在的模式在搜尋路徑中，並且沒有同名的表出現在搜尋路徑的更早的地方，那麼就說這個表視可見的。 它等效於表可以不帶明確模式修飾進行引用。

名字	描述	應用型別
pg_table_is_visible(table_oid)	該表/檢視是否在搜尋路徑中可見	regclass
pg_type_is_visible(type_oid)	該類/檢視型是否在搜尋路徑中可見	regtype
pg_function_is_visible(function_oid)	該函式是否在搜尋路徑中可見	regprocedure
pg_operator_is_visible(operator_oid)	該操作符是否在搜尋路徑中可見	regoperator
pg_opclass_is_visible(opclass_oid)	該操作符表是否在搜尋路徑中可見	regclass
pg_conversion_is_visible(conversion_oid)	轉換是否在搜尋路徑中可見	regoperator

    注：以上函式均返回boolean型別，所有這些函式都需要物件 OID 標識作為檢查的物件。
    postgres=# SELECT pg_table_is_visible('testtable'::regclass);
     pg_table_is_visible
    ---------------------
     t
    (1 row)

    4. 系統表資訊函式：

名字	返回型別	描述
format_type(type_oid,typemod)	text	獲取一個資料型別的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	為規則獲取CREATE RULE命令
pg_get_ruledef(rule_oid,pretty_bool)	text	為規則獲取CREATE RULE命令
pg_get_indexdef(index_oid)	text	為索引獲取CREATE INDEX命令
pg_get_indexdef(index_oid,column_no,pretty_bool)	text	為索引獲取CREATE INDEX命令， 如果column_no不為零，則是隻獲取一個索引欄位的定義
pg_get_triggerdef(trigger_oid)	text	為觸發器獲取CREATE [CONSTRAINT] TRIGGER
pg_get_constraintdef(constraint_oid)	text	獲取一個約束的定義
pg_get_constraintdef(constraint_oid,pretty_bool)	text	獲取一個約束的定義
pg_get_expr(expr_text,relation_oid)	text	反編譯一個表示式的內部形式，假設其中的任何Vars都引用第二個引數指出的關係
pg_get_expr(expr_text,relation_oid, pretty_bool)	text	反編譯一個表示式的內部形式，假設其中的任何Vars都引用第二個引數指出的關係
pg_get_userbyid(roleid)	name	獲取給出的ID的角色名
pg_get_serial_sequence(table_name,column_name)	text	獲取一個serial或者bigserial欄位使用的序列名字
pg_tablespace_databases(tablespace_oid)	setof oid	獲取在指定表空間(OID表示)中擁有物件的一套資料庫的OID的集合

    這些函式大多數都有兩個變種，其中一個可以選擇對結果的"漂亮的列印"。 漂亮列印的格式更容易讀，但是預設的格式更有可能被將來的PostgreSQL版本用同樣的方法解釋；如果是用於轉儲，那麼儘可能避免使用漂亮列印。 給漂亮列印引數傳遞false生成的結果和那個沒有這個引數的變種生成的結果完全一樣。

十三、系統管理函式：

    1. 查詢以及修改執行時配置引數的函式：

名字	返回型別	描述
current_setting(setting_name)	text	當前設定的值
set_config(setting_name,new_value,is_local)	text	設定引數並返回新值

    current_setting用於以查詢形式獲取setting_name設定的當前數值。它和SQL命令SHOW是等效的。 比如：
    MyTest=# SELECT current_setting('datestyle');
     current_setting
    -----------------
     ISO, YMD
    (1 row)
    set_config將引數setting_name設定為new_value。如果is_local設定為true，那麼新數值將只應用於當前事務。如果你希望新的數值應用於當前會話，那麼應該使用false。它等效於SQL命令SET。比如：
    MyTest=# SELECT set_config('log_statement_stats','off', false);
     set_config
    ------------
     off
    (1 row)
   
    2. 資料庫物件尺寸函式：

名字	返回型別	描述
pg_tablespace_size(oid)	bigint	指定OID代表的表空間使用的磁碟空間
pg_tablespace_size(name)	bigint	指定名字的表空間使用的磁碟空間
pg_database_size(oid)	bigint	指定OID代表的資料庫使用的磁碟空間
pg_database_size(name)	bigint	指定名稱的資料庫使用的磁碟空間
pg_relation_size(oid)	bigint	指定OID代表的表或者索引所使用的磁碟空間
pg_relation_size(text)	bigint	指定名稱的表或者索引使用的磁碟空間。這個名字可以用模式名修飾
pg_total_relation_size(oid)	bigint	指定OID代表的表使用的磁碟空間，包括索引和壓縮資料
pg_total_relation_size(text)	bigint	指定名字的表所使用的全部磁碟空間，包括索引和壓縮資料。表名字可以用模式名修飾。
pg_size_pretty(bigint)	text	把位元組計算的尺寸轉換成一個人類易讀的尺寸單位

    3. 資料庫物件位置函式： 

名字	返回型別	描述
pg_relation_filenode(relation regclass)	oid	獲取指定物件的檔案節點編號(通常為物件的oid值)。
pg_relation_filepath(relation regclass)	text	獲取指定物件的完整路徑名。

    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)   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9521459/viewspace-759336/，如需轉載，請註明出處，否則將追究法律責任。