1 of 29

9. 函式及運算子

PostgreSQL 為內建的資料型別提供了大量的函數和運算子。使用者還可以定義自己的函數和運算子，如第 V 部分所述。psql 指令 \df 和 \do 可分別用於列出所有可用的函數和運算子。

如果您擔心可移植性，那麼請注意，本章中描述的大多數函數和運算子（最常見的算術運算子和比較運算子以及一些明確標記的函數除外）都不是由 SQL 標準指定的。其他一些 SQL 資料庫管理系統提供了其中一些延伸功能，並且在許多情況下，這些功能在各種實作之間是相容和一致的。本章可能不夠完整；附加功能出現在手冊的其他相關章節中。

9.1. 邏輯運算子

The usual logical operators are available:

AND

OR

NOT

SQLuses a three-valued logic system with true, false, andnull, which represents“unknown”. Observe the following truth tables:

a

b

The operatorsANDandORare commutative, that is, you can switch the left and right operand without affecting the result. But seefor more information about the order of evaluation of subexpressions.

9.2. 比較函式及運算子

The usual comparison operators are available, as shown inTable 9.1.

Table 9.1. Comparison Operators

Operator

Description

<

less than

>

greater than

<=

less than or equal to

Note

The!=operator is converted to<>in the parser stage. It is not possible to implement!=and<>operators that do different things.

Comparison operators are available for all relevant data types. All comparison operators are binary operators that return values of typeboolean; expressions like1 < 2 < 3are not valid (because there is no<operator to compare a Boolean value with3).

There are also some comparison predicates, as shown in. These behave much like operators, but have special syntax mandated by the SQL standard.

Table 9.2. Comparison Predicates

TheBETWEENpredicate simplifies range tests:

is equivalent to

Notice thatBETWEENtreats the endpoint values as included in the range.NOT BETWEENdoes the opposite comparison:

is equivalent to

BETWEEN SYMMETRICis likeBETWEENexcept there is no requirement that the argument to the left ofANDbe less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied.

Ordinary comparison operators yield null (signifying“unknown”), not true or false, when either input is null. For example,7 = NULLyields null, as does7 <> NULL. When this behavior is not suitable, use theIS [NOT] DISTINCT FROMpredicates:

For non-null inputs,IS DISTINCT FROMis the same as the<>operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly,IS NOT DISTINCT FROMis identical to=for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these predicates effectively act as though null were a normal data value, rather than“unknown”.

To check whether a value is or is not null, use the predicates:

or the equivalent, but nonstandard, predicates:

Donot_writeexpression_= NULLbecauseNULLis not“equal to”NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

Tip

Some applications might expect thatexpression= NULLreturns true if_expression_evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if that cannot be done theconfiguration variable is available. If it is enabled,PostgreSQLwill convertx = NULLclauses tox IS NULL.

If theexpression_is row-valued, thenIS NULLis true when the row expression itself is null or when all the row's fields are null, whileIS NOT NULLis true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior,IS NULLandIS NOT NULLdo not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests. In some cases, it may be preferable to writerowIS DISTINCT FROM NULLorrow_IS NOT DISTINCT FROM NULL, which will simply check whether the overall row value is null without any additional tests on the row fields.

Boolean values can also be tested using the predicates

These will always return true or false, never a null value, even when the operand is null. A null input is treated as the logical value“unknown”. Notice thatIS UNKNOWNandIS NOT UNKNOWNare effectively the same asIS NULLandIS NOT NULL, respectively, except that the input expression must be of Boolean type.

Some comparison-related functions are also available, as shown in.

Table 9.3. Comparison Functions

9.3. 數學函式及運算子

Mathematical operators are provided for manyPostgreSQLtypes. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.

shows the available mathematical operators.

Table 9.4. Mathematical Operators

9.6. 二元字串函式及運算子

This section describes functions and operators for examining and manipulating bit strings, that is values of the typesbitandbit varying. Aside from the usual comparison operators, the operators shown inTable 9.13can be used. Bit string operands of&,|, and#must be of equal length. When bit shifting, the original length of the string is preserved, as shown in the examples.

Table 9.13. Bit String Operators

Operator

Description

The followingSQL-standard functions work on bit strings as well as character strings:length,bit_length,octet_length,position,substring,overlay.

The following functions work on bit strings as well as binary strings:get_bit,set_bit. When working with a bit string, these functions number the first (leftmost) bit of the string as bit 0.

In addition, it is possible to cast integral values to and from typebit. Some examples:

Note that casting to just“bit”means casting tobit(1), and so will deliver only the least significant bit of the integer.

Note

Casting an integer tobit(n)copies the rightmostnbits. Casting an integer to a bit string width wider than the integer itself will sign-extend on the left.

9.10. 列舉型別函式

For enum types (described inSection 8.7), there are several functions that allow cleaner programming without hard-coding particular values of an enum type. These are listed inTable 9.32. The examples assume an enum type created as:

Table 9.32. Enum Support Functions

Function

Description

Example

Example Result

enum_first(anyenum)

Returns the first value of the input enum type

enum_first(null::rainbow)

red

Notice that except for the two-argument form ofenum_range, these functions disregard the specific value passed to them; they care only about its declared data type. Either null or a specific value of the type can be passed, with the same result. It is more common to apply these functions to a table column or function argument than to a hardwired type name as suggested by the examples.

9.16. 序列函式

This section describes functions for operating onsequence objects, also called sequence generators or just sequences. Sequence objects are special single-row tables created withCREATE SEQUENCE. Sequence objects are commonly used to generate unique identifiers for rows of a table. The sequence functions, listed inTable 9.47, provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

Table 9.47. Sequence Functions

Function

Return Type

Description

currval(regclass)

bigint

Return value most recently obtained withnextvalfor specified sequence

The sequence to be operated on by a sequence function is specified by aregclassargument, which is simply the OID of the sequence in thepg_classsystem catalog. You do not have to look up the OID by hand, however, since theregclassdata type's input converter will do the work for you. Just write the sequence name enclosed in single quotes so that it looks like a literal constant. For compatibility with the handling of ordinarySQLnames, the string will be converted to lower case unless it contains double quotes around the sequence name. Thus:

The sequence name can be schema-qualified if necessary:

Seefor more information aboutregclass.

Note

BeforePostgreSQL8.1, the arguments of the sequence functions were of typetext, notregclass, and the above-described conversion from a text string to an OID value would happen at run time during each call. For backward compatibility, this facility still exists, but internally it is now handled as an implicit coercion fromtexttoregclassbefore the function is invoked.

When you write the argument of a sequence function as an unadorned literal string, it becomes a constant of typeregclass. Since this is really just an OID, it will track the originally identified sequence despite later renaming, schema reassignment, etc. This“early binding”behavior is usually desirable for sequence references in column defaults and views. But sometimes you might want“late binding”where the sequence reference is resolved at run time. To get late-binding behavior, force the constant to be stored as atextconstant instead ofregclass:

Note that late binding was the only behavior supported inPostgreSQLreleases before 8.1, so you might need to do this to preserve the semantics of old applications.

Of course, the argument of a sequence function can be an expression as well as a constant. If it is a text expression then the implicit coercion will result in a run-time lookup.

The available sequence functions are:

nextval

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions executenextvalconcurrently, each will safely receive a distinct sequence value.

If a sequence object has been created with default parameters, successivenextvalcalls will return successive values beginning with 1. Other behaviors can be obtained by using special parameters in thecommand; see its command reference page for more information.

Important

To avoid blocking concurrent transactions that obtain numbers from the same sequence, anextvaloperation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example anINSERTwith anON CONFLICTclause will compute the to-be-inserted tuple, including doing any requirednextvalcalls, before detecting any conflict that would cause it to follow theON CONFLICTrule instead. Such cases will leave unused“holes”in the sequence of assigned values. Thus,PostgreSQLsequence objectscannot be used to obtain“gapless”sequences.

This function requiresUSAGEorUPDATEprivilege on the sequence.

currval

Return the value most recently obtained bynextvalfor this sequence in the current session. (An error is reported ifnextvalhas never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executednextvalsince the current session did.

This function requiresUSAGEorSELECTprivilege on the sequence.

lastval

Return the value most recently returned bynextvalin the current session. This function is identical tocurrval, except that instead of taking the sequence name as an argument it refers to whichever sequencenextvalwas most recently applied to in the current session. It is an error to calllastvalifnextvalhas not yet been called in the current session.

This function requiresUSAGEorSELECTprivilege on the last used sequence.

setval

Reset the sequence object's counter value. The two-parameter form sets the sequence'slast_valuefield to the specified value and sets itsis_calledfield totrue, meaning that the nextnextvalwill advance the sequence before returning a value. The value reported bycurrvalis also set to the specified value. In the three-parameter form,is_calledcan be set to eithertrueorfalse.truehas the same effect as the two-parameter form. If it is set tofalse, the nextnextvalwill return exactly the specified value, and sequence advancement commences with the followingnextval

The result returned bysetvalis just the value of its second argument.

Important

Because sequences are non-transactional, changes made bysetvalare not undone if the transaction rolls back.

This function requiresUPDATEprivilege on the sequence.

9.17. 條件表示式

本節介紹 PostgreSQL 中符合 SQL 標準可用的條件表示式。

提示

如果您的需求超出了這些條件表示式的功能，您可能需要考慮使用功能更強的程序語言編寫 stored procedure。

9.22. 子查詢

This section describes theSQL-compliant subquery expressions available inPostgreSQL. All of the expression forms documented in this section return Boolean (true/false) results.

9.22.1. `EXISTS`

The argument ofEXISTSis an arbitrarySELECTstatement, orsubquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result ofEXISTS

9.23. 資料列與陣列的比較運算

本節介紹了用於在多群組內容之間進行多重比較的幾個專用語法結構。這些功能在語法上與前一節的子查詢形式相關，但不涉及子查詢。涉及陣列子表示式的形式是 PostgreSQL 的延伸功能；其餘的都是相容 SQL 的。本節中記錄的所有表達形式都是回傳布林值（true/false）結果。

9.23.1. `IN`

右側是 scalar 表示式帶括號的列表。如果左側表示式的結果等於任何右側表示式，結果為「true」。這是一個簡寫的方式

請注意，如果左側表示式產生空值，或者沒有相等的右側值並且至少有一個右側表示式產生空值，則 IN 的的結果將為空，而不是 false。這符合 SQL 空值布林組合的普遍規則。

9.23.2. `NOT IN`

右側是 scalar 表示式帶括號的列表。如果左側表示式的結果不等於所有右側表示式，則結果為「true」。這是一個簡寫的方式

請注意，如果左邊的表示式為空，或者沒有相等的右邊的值，並且至少有一個右邊的表示式為空，則 NOT IN 的結果將為空，而不要天真地認為是 true。這符合 SQL 空值布林組合的普遍規則。

小技巧

x NOT IN y 在所有情況下都等於 NOT（x IN y）。但是，使用 NOT IN 時，與使用 IN 時相比，空值更有可能讓新手感到痛苦。如果可能的話，最好積極轉換自己需要的比較內容。

9.23.3. `ANY`/`SOME` (array)

右側是一個帶括號的表示式，它必須產生一個陣列。使用給定的運算子評估左側表示式並與陣列的每個元素進行比較，該運算子必須產生布林結果。如果獲得任何 true 結果，則 ANY 的結果為「true」。如果未找到 true（包括陣列中沒有元素的情況），則結果為「false」。

如果陣列表示式產生一個空的陣列，則 ANY 的結果將為空。如果左邊的表示式為空，則 ANY 的結果通常為空（儘管非嚴格的比較運算子可能會產生不同的結果）。另外，如果右邊的陣列包含任何空元素並且沒有獲得真正的比較結果，則 ANY 的結果將為空，而不是 false（再次假設嚴格的比較運算子）。這符合 SQL 空值布林組合的普遍規則。

SOME 是 ANY 的同義詞。

9.23.4. `ALL` (array)

右側是一個帶括號的表示式，它必須產生一個陣列。使用給定的運算子計算左側表示式並與陣列的每個元素進行比較，該運算子必須產生布林結果。如果所有比較都為真（包括陣列為空的情況），則 ALL 的結果為“真”。如果發現任何錯誤的情況，結果就為“假”。

如果陣列表示式產生一個空陣列，則 ALL 的結果將為 NULL。如果左邊的表示式為NULL，則 ALL 的結果通常為 NULL（儘管非嚴格的比較運算子可能產生不同的結果）。另外，如果右邊的陣列包含任何 NULL 元素，並且沒有獲得錯誤的比較結果，則 ALL 的結果將為 NULL，而不是 TRUE（再次假設一個嚴格的比較運算子）。這符合 SQL NULL 布林組合的一般性規則。

9.23.5. 資料列行建構函數比較

每一邊都是資料列建構函數，如 4.2.13 節所述。兩個資料列內容必須具有相同的欄位數。運算好每一側，並逐個資料列比較它們。當運算子為 =，<>，<，<=，>或 >=時，允許進行資料列建構函數比較。每個資料列元素必須是具有預設 B-tree運算子類的型別，否則嘗試的比較可能會産生錯誤。

注意如果使用前面的欄位解析比較，則可能不會發生與元素數量或型別相關的錯誤。

= 和 <> 比較的工作方式與其他比較略有不同。如果所有相應的成員都是非空且相等的，則認為兩個資料列相等；如果任何相應的成員非空且不相等，則資料列不相等；否則資料列比較的結果是未知的（null）。

對於 <，<=，> 和 >= 情況，資料列元素從左到右進行比較，一旦找到不相等或空的元素配對就停止。如果這對元素中的任何一個為 null，則資料列比較的結果是未知的（null）；否則這對元素的比較就決定了結果。例如，ROW(1, 2, NULL) < ROW(1, 3, 0) 產生 true，而不是 null，因為不考慮第三組元素。

注意在 PostgreSQL 8.2 之前，每個 SQL 規範都沒有處理 <，<=，> 和 >=。像ROW(a, b) < ROW(c, d) 這樣的比較被實作為 a < c AND b < d，而正確的行為等同於 a < c OR (a = c AND b <d)。

此語法類似於 <> 行比較，但它不會因為 null 輸入產生 null。相反地，任何空值被認為不等於（不同於）任何非空值，並且任何兩個空值被認為是相等的（不是不同的）。因此結果將為 true 或 false，永遠不為 null。

此語法類似於 a = 資料列比較，但它不會因為 null 輸入而產生 null。相反地，任何空值被認為不等於（不同於）任何非空值，並且任何兩個空值被認為是相等的（不是不同的）。因此，結果將始終為 true 或 false，永遠不會為 null。

9.23.6. 複合型別比較

如果結果取決於比較兩個 NULL 值或 NULL 和非 NULL，則 SQL 規範要求按資料列進行比較以回傳 NULL。PostgreSQL只在比較兩個資料列建構函數的結果（如）或者將一個資料列建構函數與子查詢的輸出結果進行比較時（如）那樣做。在比較兩個複合型別內容的其他部份中，兩個 NULL 字串會被認為是相等的，並且 NULL 被認為大於非 NULL。為了對複合型別進行一致的排序和索引行為，這是必須的。

評估每一側，並逐個資料列比較它們。當運算符為 =，<>，<，<=，> 或 >= 時，允許複合型別比較，或者俱有與其中一個類似的語義。（具體而言，如果一個運算子是 B-Tree 運算子類的成員，或者是 B-Tree 運算子類的 = 成員的否定運算，則它可以是資料列比較運算子。）上述運算子的預設行為與資料列建構函數的 IS [NOT] DISTINCT FROM 相同（見）。

為了支援包含沒有預設 B-Tree 運算子類的元素的資料列匹配，以下運算子被定義用於複合型別比較： =， <>， <， <=，> 和 >=。這些運算子比較兩個資料列的內部二進製表示形式。即使兩個資料列與等號運算子的比較為真，兩個資料列也可能具有不同的二進製表示形式。這些比較運算子下的資料列排序是確定性的，但沒有其他意義。這些運算子在內部用於具體化檢視表，並可用於其他專用目的（如複寫），但不打算經常用於撰寫查詢。

9.27. 觸發函式

CurrentlyPostgreSQLprovides one built in trigger function,suppress_redundant_updates_trigger, which will prevent any update that does not actually change the data in the row from taking place, in contrast to the normal behavior which always performs the update regardless of whether or not the data has changed. (This normal behavior makes updates run faster, since no checking is required, and is also useful in certain cases.)

Ideally, you should normally avoid running updates that don't actually change the data in the record. Redundant updates can cost considerable unnecessary time, especially if there are lots of indexes to alter, and space in dead rows that will eventually have to be vacuumed. However, detecting such situations in client code is not always easy, or even possible, and writing expressions to detect them can be error-prone. An alternative is to usesuppress_redundant_updates_trigger, which will skip updates that don't change the data. You should use this with care, however. The trigger takes a small but non-trivial time for each record, so if most of the records affected by an update are actually changed, use of this trigger will actually make the update run slower.

Thesuppress_redundant_updates_triggerfunction can be added to a table like this:

CREATE TRIGGER z_min_update
BEFORE UPDATE ON tablename
FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();

In most cases, you would want to fire this trigger last for each row. Bearing in mind that triggers fire in name order, you would then choose a trigger name that comes after the name of any other trigger you might have on the table.

For more information about creating triggers, see .

9.16. 序列函式

Table 9.47. Sequence Functions

Function

Return Type

Description

currval(regclass)

bigint

Return value most recently obtained withnextvalfor specified sequence

The sequence name can be schema-qualified if necessary:

Seefor more information aboutregclass.

Note

Note that late binding was the only behavior supported inPostgreSQLreleases before 8.1, so you might need to do this to preserve the semantics of old applications.

Of course, the argument of a sequence function can be an expression as well as a constant. If it is a text expression then the implicit coercion will result in a run-time lookup.

The available sequence functions are:

nextval

Important

This function requiresUSAGEorUPDATEprivilege on the sequence.

currval

This function requiresUSAGEorSELECTprivilege on the sequence.

lastval

This function requiresUSAGEorSELECTprivilege on the last used sequence.

setval

The result returned bysetvalis just the value of its second argument.

Important

Because sequences are non-transactional, changes made bysetvalare not undone if the transaction rolls back.

This function requiresUPDATEprivilege on the sequence.

9.14. XML函式

版本：11

本節中描述的函數和類函數表示式對 xml 型別的值進行操作。有關 xml 型別的訊息，請查看第 8.13 節。這裡不再重複用於轉換為 xml 型別的函數表示式 xmlparse 和 xmlserialize。使用大多數這些函數需要使用 configure --with-libxml 編譯安裝。

9.14.1. 産生 XML 內容

一組函數和類函數的表示式可用於從 SQL 資料産生 XML 內容。因此，它們特別適合將查詢結果格式化為 XML 文件以便在用戶端應用程序中進行處理。

9.14.1.1. xmlcomment

函數 xmlcomment 建立一個 XML 字串，其中包含指定文字作為內容的 XML 註釋。文字不能包含「 -- 」或以「 - 」結尾，以便産生的結構是有效的 XML 註釋。如果參數為 null，則結果為 null。

例如：

9.14.1.2. xmlconcat

函數 xmlconcat 連接列表中各個 XML 字串，以建立包含 XML 內容片段的單個字串。空值會被忽略；如果都沒有非空值參數，則結果僅為 null。

例如：

XML 宣告（如果存在）組合如下。如果所有參數值具有相同的 XML 版本宣告，則在結果中使用該版本，否則不使用任何版本。如果所有參數值都具有獨立宣告值「yes」，則在結果中使用該值。如果所有參數值都具有獨立的宣告值且至少有一個為「no」，則在結果中使用該值。否則結果將沒有獨立宣告。如果確定結果需要獨立宣告但沒有版本聲明，則將使用版本為 1.0 的版本宣告，因為 XML 要求 XML 宣告包含版本宣告。在所有情況下都會忽略編碼宣告並將其刪除。

例如：

9.14.1.3. xmlelement

xmlelement 表示式産生具有給定名稱、屬性和內容的 XML 元素。

範例：

透過用 xHHHH 序列替換有問題的字符來轉譯非有效 XML 名稱的元素和屬性名稱，其中 HHHH 是十六進位表示法中字元的 Unicode 代碼。例如：

如果屬性值是引用欄位，則無需明確指定屬性名稱，在這種情況下，預設情況下欄位的名稱將用作屬性名稱。在其他情況下，必須為該屬性明確指定名稱。所以這個例子是有效的：

但這些不行：

元素內容（如果已指定）將根據其資料型別進行格式化。如果內容本身是 xml 型別，則可以建構複雜的 XML 文件。例如：

其他型別的內容將被格式化為有效的 XML 字元資料。這尤其意味著字符 <、> 和＆將被轉換為其他形式。二進位資料（資料型別 bytea）將以 base64 或十六進位編碼表示，具體取決於組態參數 xmlbinary 的設定。為了使 SQL 和 PostgreSQL 資料型別與 XML Schema 規範保持一致，預計各種資料型別的特定行為將會各自發展，此時將出現更精確的描述。

9.14.1.4. xmlforest

xmlforest 表示式使用給定的名稱和內容産生元素的 XML 序列。

範例：

如第二個範例所示，如果內容值是欄位引用，則可以省略元素名稱，在這種情況下，預設情況下使用欄位名稱。否則，必須指定名稱。

非有效的 XML 名稱的元素名稱將被轉譯，如上面的 xmlelement 所示。類似地，內容資料會被轉譯以産生有效的 XML 內容，除非它已經是 xml 型別。

請注意，如果 XML 序列由多個元素組成，則它們不是有效的 XML 文件，因此將 xmlforest 表示式包裝在 xmlelement 中可能很有用。

9.14.1.5. xmlpi

xmlpi 表示式建立 XML 處理指令。內容（如果存在）不得包含字元序列 ?>。

例如：

9.14.1.6. xmlroot

xmlroot 表示式改變 XML 值的根節點屬性。如果指定了版本，它將替換根節點的版本宣告中的值；如果指定了獨立設定，則它將替換根節點的獨立宣告中的值。

9.14.1.7. xmlagg

與此處描述的其他函數不同，函數 xmlagg 是一個彙總函數。它將輸入值連接到彙總函數呼叫，就像 xmlconcat 一樣，除了它是跨資料列而不是在單個資料列中的表示式進行連接。有關彙總函數的其他訊息，請參閱。

例如：

要確定連接的順序，可以將 ORDER BY 子句加到彙總呼叫中，如第 4.2.7 節中所述。例如：

以前的版本中推薦使用以下非標準方法，在特定情況下可能仍然有用：

9.14.2. XML Predicates

本節中描述的表示式用於檢查 xml 的屬性。

9.14.2.1. IS DOCUMENT

如果參數 XML 是正確的 XML 文件，則表示式 IS DOCUMENT 將回傳 true，如果不是（它是內容片段），則回傳 false；如果參數為 null，則回傳 null。有關文件和內容片段之間的區別，請參閱。

9.14.2.2. XMLEXISTS

如果第一個參數中的 XPath 表示式回傳任何節點，則 xmlexists 函數回傳 true，否則回傳 false。（如果任一參數為 null，則結果為 null。）

範例

BY REF 子句在 PostgreSQL 中沒有任何作用，但可以達到 SQL 一致性和與其他實作的相容性。根據 SQL 標準，第一個 BY REF 是必需的，第二個是選擇性的。另請注意，SQL 標準指定 xmlexists 構造將 XQuery 表示式作為第一個參數，但 PostgreSQL 目前僅支持 XPath，它是 XQuery 的子集。

9.14.2.3. xml_is_well_formed

此函數檢查文字字串是否格式正確，回傳布林結果。xml_is_well_formed_document 檢查格式正確的文檔，而 xml_is_well_formed_content 檢查格式良好的內容。如果 xmloption 配置參數設定為 DOCUMENT，則 xml_is_well_formed 會執行前者；如果設定為 CONTENT，則執行後者。這意味著 xml_is_well_formed 對於查看對 xml 類型的簡單強制轉換是否成功很有用，而其他兩個函數對於查看 XMLPARSE 的相對應變數是否成功很有用。

範例：

最後一個範例顯示檢查包括命名空間是否符合。

9.14.3. 處理 XML

為了處理資料型別為 xml 的值，PostgreSQL 提供了 xpath 和 xpath_exists 函數，它們用於計算 XPath 1.0 表示式和 XMLTABLE 資料表函數。

9.14.3.1. xpath

函數 xpath 根據 XML 值 xml 計算 XPath 表示式 xpath（字串）。它回傳與 XPath 表示式產生的節點集合所相對應 XML 值的陣列。如果 XPath 表示式回傳單一變數值而不是節點集合，則回傳單個元素的陣列。

第二個參數必須是格式良好的 XML 內容。特別要注意是，它必須具有單一根節點元素。

該函數的選擇性第三個參數是命名空間對應的陣列。該陣列應該是二維字串陣列，第二維的長度等於 2（即，它應該是陣列的陣列，每個陣列恰好由 2 個元素組成）。每個陣列項目的第一個元素是命名空間名稱（別名），第二個是命名空間 URI。不要求此陣列中提供的別名與 XML 內容本身所使用的別名相同（換句話說，在 XML 內容和 xpath 函數內容中，別名都是區域性的）。

例如：

要設定預設的（匿名）命名空間，請執行以下操作：

9.14.3.2. xpath_exists

The function xpath_exists is a specialized form of the xpath function. Instead of returning the individual XML values that satisfy the XPath, this function returns a Boolean indicating whether the query was satisfied or not. This function is equivalent to the standard XMLEXISTS predicate, except that it also offers support for a namespace mapping argument.

Example:

9.14.3.3. xmltable

The xmltable function produces a table based on the given XML value, an XPath filter to extract rows, and an optional set of column definitions.

The optional XMLNAMESPACES clause is a comma-separated list of namespaces. It specifies the XML namespaces used in the document and their aliases. A default namespace specification is not currently supported.

The required row_expression argument is an XPath expression that is evaluated against the supplied XML document to obtain an ordered sequence of XML nodes. This sequence is what xmltable transforms into output rows.

document_expression provides the XML document to operate on. The BY REF clauses have no effect in PostgreSQL, but are allowed for SQL conformance and compatibility with other implementations. The argument must be a well-formed XML document; fragments/forests are not accepted.

The mandatory COLUMNS clause specifies the list of columns in the output table. If the COLUMNS clause is omitted, the rows in the result set contain a single column of type xml containing the data matched by row_expression. If COLUMNS is specified, each entry describes a single column. See the syntax summary above for the format. The column name and type are required; the path, default and nullability clauses are optional.

A column marked FOR ORDINALITY will be populated with row numbers matching the order in which the output rows appeared in the original input XML document. At most one column may be marked FOR ORDINALITY.

The column_expression for a column is an XPath expression that is evaluated for each row, relative to the result of the row_expression, to find the value of the column. If no column_expression is given, then the column name is used as an implicit path.

If a column's XPath expression returns multiple elements, an error is raised. If the expression matches an empty tag, the result is an empty string (not NULL). Any xsi:nil attributes are ignored.

The text body of the XML matched by the column_expression is used as the column value. Multiple text() nodes within an element are concatenated in order. Any child elements, processing instructions, and comments are ignored, but the text contents of child elements are concatenated to the result. Note that the whitespace-only text() node between two non-text elements is preserved, and that leading whitespace on a text() node is not flattened.

If the path expression does not match for a given row but default_expression is specified, the value resulting from evaluating that expression is used. If no DEFAULT clause is given for the column, the field will be set to NULL. It is possible for a default_expression to reference the value of output columns that appear prior to it in the column list, so the default of one column may be based on the value of another column.

Columns may be marked NOT NULL. If the column_expression for a NOT NULL column does not match anything and there is no DEFAULT or the default_expression also evaluates to null, an error is reported.

Unlike regular PostgreSQL functions, column_expression and default_expression are not evaluated to a simple value before calling the function. column_expression is normally evaluated exactly once per input row, and default_expression is evaluated each time a default is needed for a field. If the expression qualifies as stable or immutable the repeat evaluation may be skipped. Effectively xmltable behaves more like a subquery than a function call. This means that you can usefully use volatile functions like nextval in default_expression, and column_expression may depend on other parts of the XML document.

Examples:

The following example shows concatenation of multiple text() nodes, usage of the column name as XPath filter, and the treatment of whitespace, XML comments and processing instructions:

The following example illustrates how the XMLNAMESPACES clause can be used to specify the default namespace, and a list of additional namespaces used in the XML document as well as in the XPath expressions:

9.14.4. Mapping Tables to XML

The following functions map the contents of relational tables to XML values. They can be thought of as XML export functionality:

The return type of each function is xml.

table_to_xml maps the content of the named table, passed as parameter tbl. The regclass type accepts strings identifying tables using the usual notation, including optional schema qualifications and double quotes. query_to_xml executes the query whose text is passed as parameter query and maps the result set. cursor_to_xml fetches the indicated number of rows from the cursor specified by the parameter cursor. This variant is recommended if large tables have to be mapped, because the result value is built up in memory by each function.

If tableforest is false, then the resulting XML document looks like this:

If tableforest is true, the result is an XML content fragment that looks like this:

If no table name is available, that is, when mapping a query or a cursor, the string table is used in the first format, row in the second format.

The choice between these formats is up to the user. The first format is a proper XML document, which will be important in many applications. The second format tends to be more useful in the cursor_to_xml function if the result values are to be reassembled into one document later on. The functions for producing XML content discussed above, in particular xmlelement, can be used to alter the results to taste.

The data values are mapped in the same way as described for the function xmlelement above.

The parameter nulls determines whether null values should be included in the output. If true, null values in columns are represented as:

where xsi is the XML namespace prefix for XML Schema Instance. An appropriate namespace declaration will be added to the result value. If false, columns containing null values are simply omitted from the output.

The parameter targetns specifies the desired XML namespace of the result. If no particular namespace is wanted, an empty string should be passed.

The following functions return XML Schema documents describing the mappings performed by the corresponding functions above:

It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.

The following functions produce XML data mappings and the corresponding XML Schema in one document (or forest), linked together. They can be useful where self-contained and self-describing results are wanted:

In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:

Note that these potentially produce a lot of data, which needs to be built up in memory. When requesting content mappings of large schemas or databases, it might be worthwhile to consider mapping the tables separately instead, possibly even through a cursor.

The result of a schema content mapping looks like this:

where the format of a table mapping depends on the tableforest parameter as explained above.

The result of a database content mapping looks like this:

where the schema mapping is as above.

As an example of using the output produced by these functions, shows an XSLT stylesheet that converts the output of table_to_xml_and_xmlschema to an HTML document containing a tabular rendition of the table data. In a similar manner, the results from these functions can be converted into other XML-based formats.

Figure 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML

9.9 日期時間函式及運算子

Table 9-28 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9-27 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.

All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.

Table 9-27. Date/Time Operators

Operator

Example

Result

Table 9-28. Date/Time Functions

In addition to these functions, the SQL OVERLAPS operator is supported:

This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'. For example, with the session time zone set to CST7CDT, timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' will produce timestamp with time zone '2005-04-03 12:00-06', while adding interval '24 hours' to the same initial timestamp with time zone produces timestamp with time zone '2005-04-03 13:00-06', as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.

Note there can be ambiguity in the months field returned by age because different months have different numbers of days. PostgreSQL's approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30.

Subtraction of dates and timestamps can also be complex. One conceptually simple way to perform subtraction is to convert each value to a number of seconds using EXTRACT(EPOCH FROM ...), then subtract the results; this produces the number of seconds between the two values. This will adjust for the number of days in each month, timezone changes, and daylight saving time adjustments. Subtraction of date or timestamp values with the "-" operator returns the number of days (24-hours) and hours/minutes/seconds between the values, making the same adjustments. The age function returns years, months, days, and hours/minutes/seconds, performing field-by-field subtraction and then adjusting for negative field values. The following queries illustrate the differences in these approaches. The sample results were produced with timezone = 'US/Eastern'; there is a daylight saving time change between the two dates used:

9.9.1. `EXTRACT`, `date_part`

The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type dateare cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names:century

The century

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.day

For timestamp values, the day (of the month) field (1 - 31) ; for interval values, the number of days

decade

The year field divided by 10

dow

The day of the week as Sunday (0) to Saturday (6)

Note that extract's day of the week numbering differs from that of the to_char(..., 'D') function.doy

The day of the year (1 - 365/366)

epoch

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

Here is how you can convert an epoch value back to a time stamp:

(The to_timestamp function encapsulates the above conversion.)hour

The hour field (0 - 23)

isodow

The day of the week as Monday (1) to Sunday (7)

This is identical to dow except for Sunday. This matches the ISO 8601 day of the week numbering.isoyear

The ISO 8601 week-numbering year that the date falls in (not applicable to intervals)

Each ISO 8601 week-numbering year begins with the Monday of the week containing the 4th of January, so in early January or late December the ISO year may be different from the Gregorian year. See the week field for more information.

This field is not available in PostgreSQL releases prior to 8.3.microseconds

The seconds field, including fractional parts, multiplied by 1 000 000; note that this includes full seconds

millennium

The millennium

Years in the 1900s are in the second millennium. The third millennium started January 1, 2001.milliseconds

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

minute

The minutes field (0 - 59)

month

For timestamp values, the number of the month within the year (1 - 12) ; for interval values, the number of months, modulo 12 (0 - 11)

quarter

The quarter of the year (1 - 4) that the date is in

second

The seconds field, including fractional parts (0 - 59)

timezone

The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not handled.)timezone_hour

The hour component of the time zone offsettimezone_minute

The minute component of the time zone offsetweek

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.

In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It's recommended to use the isoyear field together with week to get consistent results.

year

The year field. Keep in mind there is no 0 AD, so subtracting BC years from AD years should be done with care.

The extract function is primarily intended for computational processing. For formatting date/time values for display, see .

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

Note that here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract.

9.9.2. `date_trunc`

The function date_trunc is conceptually similar to the trunc function for numbers.

source is a value expression of type timestamp or interval. (Values of type date and time are cast automatically to timestamp or interval, respectively.) field selects to which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

Valid values for field are:

Examples:

9.9.3. AT TIME ZONE

The AT TIME ZONE construct allows conversions of time stamps to different time zones. shows its variants.

Table 9-29. AT TIME ZONE Variants

In these expressions, the desired time zone zone can be specified either as a text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described in .

Examples (assuming the local time zone is PST8PDT):

The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

9.9.4. Current Date/Time

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

Some examples:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.

Note: Other database systems might advance these values more frequently.

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() is a traditional PostgreSQL equivalent to transaction_timestamp().

All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:

Tip: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion.

9.9.5. Delaying Execution

The following functions are available to delay execution of the server process:

pg_sleep makes the current session's process sleep until seconds seconds have elapsed. seconds is a value of type double precision, so fractional-second delays can be specified. pg_sleep_for is a convenience function for larger sleep times specified as an interval. pg_sleep_until is a convenience function for when a specific wake-up time is desired. For example:

Note: The effective resolution of the sleep interval is platform-specific; 0.01 seconds is a common value. The sleep delay will be at least as long as specified. It might be longer depending on factors such as server load. In particular, pg_sleep_until is not guaranteed to wake up exactly at the specified time, but it will not wake up any earlier.

Notes

9.7. 特徵比對

版本：11

PostgreSQL 提供了三種不同的特徵比對方法：傳統的 SQL LIKE 運算子，最新的 SIMILAR TO 運算子（於 SQL：1999 中加入）和 POSIX 樣式的正規表示式。除了基本的「這個字串符合這個樣式嗎？」運算子之外，還可以使用函數來提取或替換符合的子字串，以及在配對的位置拆分字串。

提醒如果您的特徵比對需求超出此範圍，請考慮在 Perl 或 Tcl 中撰寫使用者定義的函數。

注意

雖然大多數正規表示式搜尋可以非常快速地執行，但是完成正規表示式需要花費大量的時間和記憶體來處理。要特別注意從各種來源接受正規表示式的搜尋方式。如果必須這樣做，建議強制限制執行語句執行時間。

使用 SIMILAR TO 方式的搜尋具有相同的安全隱憂，因為 SIMILAR TO 提供了許多與 POSIX 樣式的正規表示式相同功能。

LIKE 搜尋比其他兩個選項要簡單得多，在使用可能惡意的來源時更安全。

9.7.1. `LIKE`

The LIKE expression returns true if the string matches the supplied pattern. (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).)

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

Some examples:

LIKE pattern matching always covers the entire string. Therefore, if it's desired to match a sequence anywhere within a string, the pattern must start and end with a percent sign.

To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.

Note

If you have turned off, any backslashes you write in literal string constants will need to be doubled. See for more information.

It's also possible to select no escape character by writing ESCAPE ''. This effectively disables the escape mechanism, which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern.

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

There is also the prefix operator ^@ and corresponding starts_with function which covers cases when only searching by beginning of the string is needed.

9.7.2. `SIMILAR TO` Regular Expressions

The SIMILAR TO operator returns true or false depending on whether its pattern matches the given string. It is similar to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression. SQL regular expressions are a curious cross between LIKE notation and common regular expression notation.

Like LIKE, the SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression behavior where the pattern can match any part of the string. Also like LIKE, SIMILAR TO uses _ and % as wildcard characters denoting any single character and any string, respectively (these are comparable to . and .* in POSIX regular expressions).

In addition to these facilities borrowed from LIKE, SIMILAR TO supports these pattern-matching metacharacters borrowed from POSIX regular expressions:

| denotes alternation (either of two alternatives).
* denotes repetition of the previous item zero or more times.
+ denotes repetition of the previous item one or more times.

Notice that the period (.) is not a metacharacter for SIMILAR TO.

As with LIKE, a backslash disables the special meaning of any of these metacharacters; or a different escape character can be specified with ESCAPE.

Some examples:

The substring function with three parameters, substring(string from pattern for escape-character), provides extraction of a substring that matches an SQL regular expression pattern. As with SIMILAR TO, the specified pattern must match the entire data string, or else the function fails and returns null. To indicate the part of the pattern that should be returned on success, the pattern must contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these markers is returned.

Some examples, with #" delimiting the return string:

9.7.3. POSIX Regular Expressions

lists the available operators for pattern matching using POSIX regular expressions.

Table 9.14. Regular Expression Match Operators

POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. Many Unix tools such as egrep, sed, or awk use a pattern matching language that is similar to the one described here.

A regular expression is a character sequence that is an abbreviated definition of a set of strings (a regular set). A string is said to match a regular expression if it is a member of the regular set described by the regular expression. As with LIKE, pattern characters match string characters exactly unless they are special characters in the regular expression language — but regular expressions use different special characters than LIKE does. Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string.

Some examples:

The POSIX pattern language is described in much greater detail below.

The substring function with two parameters, substring(string from pattern), provides extraction of a substring that matches a POSIX regular expression pattern. It returns null if there is no match, otherwise the portion of the text that matched the pattern. But if the pattern contains any parentheses, the portion of the text that matched the first parenthesized subexpression (the one whose left parenthesis comes first) is returned. You can put parentheses around the whole expression if you want to use parentheses within it without triggering this exception. If you need parentheses in the pattern before the subexpression you want to extract, see the non-capturing parentheses described below.

Some examples:

The regexp_replace function provides substitution of new text for substrings that match POSIX regular expression patterns. It has the syntax regexp_replace(source, pattern, replacement [, flags ]). The source string is returned unchanged if there is no match to the pattern. If there is a match, the source string is returned with the replacement string substituted for the matching substring. The replacement

Some examples:

The regexp_match function returns a text array of captured substring(s) resulting from the first match of a POSIX regular expression pattern to a string. It has the syntax regexp_match(string, pattern [, flags ]). If there is no match, the result is NULL. If a match is found, and the pattern contains no parenthesized subexpressions, then the result is a single-element text array containing the substring matching the whole pattern. If a match is found, and the pattern contains parenthesized subexpressions, then the result is a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the

Some examples:

In the common case where you just want the whole matching substring or NULL for no match, write something like

The regexp_matches function returns a set of text arrays of captured substring(s) resulting from matching a POSIX regular expression pattern to a string. It has the same syntax as regexp_match. This function returns no rows if there is no match, one row if there is a match and the g flag is not given, or N rows if there are N matches and the g flag is given. Each returned row is a text array containing the whole matched substring or the substrings matching parenthesized subexpressions of the pattern, just as described above for regexp_match. regexp_matches accepts all the flags shown in , plus the g flag which commands it to return all matches, not just the first one.

Some examples:

Tip

In most cases regexp_matches() should be used with the g flag, since if you only want the first match, it's easier and more efficient to use regexp_match(). However,regexp_match() only exists in PostgreSQL version 10 and up. When working in older versions, a common trick is to place a regexp_matches() call in a sub-select, for example:

This produces a text array if there's a match, or NULL if not, the same as regexp_match()would do. Without the sub-select, this query would produce no output at all for table rows without a match, which is typically not the desired behavior.

The regexp_split_to_table function splits a string using a POSIX regular expression pattern as a delimiter. It has the syntax regexp_split_to_table(string, pattern [, flags ]). If there is no match to the pattern, the function returns the string. If there is at least one match, for each match it returns the text from the end of the last match (or the beginning of the string) to the beginning of the match. When there are no more matches, it returns the text from the end of the last match to the end of the string. The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. regexp_split_to_table supports the flags described in .

The regexp_split_to_array function behaves the same as regexp_split_to_table, except that regexp_split_to_array returns its result as an array of text. It has the syntax regexp_split_to_array(string, pattern [, flags ]). The parameters are the same as for regexp_split_to_table.

Some examples:

As the last example demonstrates, the regexp split functions ignore zero-length matches that occur at the start or end of the string or immediately after a previous match. This is contrary to the strict definition of regexp matching that is implemented by regexp_match and regexp_matches, but is usually the most convenient behavior in practice. Other software systems such as Perl use similar definitions.

9.7.3.1. Regular Expression Details

PostgreSQL's regular expressions are implemented using a software package written by Henry Spencer. Much of the description of regular expressions below is copied verbatim from his manual.

Regular expressions (REs), as defined in POSIX 1003.2, come in two forms: extended REs or EREs (roughly those of egrep), and basic REs or BREs (roughly those of ed). PostgreSQL supports both forms, and also implements some extensions that are not in the POSIX standard, but have become widely used due to their availability in programming languages such as Perl and Tcl. REs using these non-POSIX extensions are called advanced REs or AREs in this documentation. AREs are almost an exact superset of EREs, but BREs have several notational incompatibilities (as well as being much more limited). We first describe the ARE and ERE forms, noting features that apply only to AREs, and then describe how BREs differ.

Note

PostgreSQL always initially presumes that a regular expression follows the ARE rules. However, the more limited ERE or BRE rules can be chosen by prepending an embedded option to the RE pattern, as described in . This can be useful for compatibility with applications that expect exactly the POSIX 1003.2 rules.

A regular expression is defined as one or more branches, separated by |. It matches anything that matches one of the branches.

A branch is zero or more quantified atoms or constraints, concatenated. It matches a match for the first, followed by a match for the second, etc; an empty branch matches the empty string.

A quantified atom is an atom possibly followed by a single quantifier. Without a quantifier, it matches a match for the atom. With a quantifier, it can match some number of matches of the atom. An atom can be any of the possibilities shown in . The possible quantifiers and their meanings are shown in .

A constraint matches an empty string, but matches only when specific conditions are met. A constraint can be used where an atom could be used, except it cannot be followed by a quantifier. The simple constraints are shown in ; some more constraints are described later.

Table 9.15. Regular Expression Atoms

An RE cannot end with a backslash (\).

Note

If you have turned off, any backslashes you write in literal string constants will need to be doubled. See for more information.

Table 9.16. Regular Expression Quantifiers

The forms using {...} are known as bounds. The numbers m and n within a bound are unsigned decimal integers with permissible values from 0 to 255 inclusive.

Non-greedy quantifiers (available in AREs only) match the same possibilities as their corresponding normal (greedy) counterparts, but prefer the smallest number rather than the largest number of matches. See for more detail.

Note

A quantifier cannot immediately follow another quantifier, e.g., ** is invalid. A quantifier cannot begin an expression or subexpression or follow ^ or |.

Table 9.17. Regular Expression Constraints

Lookahead and lookbehind constraints cannot contain back references (see ), and all parentheses within them are considered non-capturing.

9.7.3.2. Bracket Expressions

A bracket expression is a list of characters enclosed in []. It normally matches any single character from the list (but see below). If the list begins with ^, it matches any single character not from the rest of the list. If two characters in the list are separated by -, this is shorthand for the full range of characters between those two (inclusive) in the collating sequence, e.g., [0-9] in ASCII matches any decimal digit. It is illegal for two ranges to share an endpoint, e.g., a-c-e. Ranges are very collating-sequence-dependent, so portable programs should avoid relying on them.

To include a literal ] in the list, make it the first character (after ^, if that is used). To include a literal -, make it the first or last character, or the second endpoint of a range. To use a literal - as the first endpoint of a range, enclose it in [. and .] to make it a collating element (see below). With the exception of these characters, some combinations using [ (see next paragraphs), and escapes (AREs only), all other special characters lose their special significance within a bracket expression. In particular, \ is not special when following ERE or BRE rules, though it is special (as introducing an escape) in AREs.

Within a bracket expression, a collating element (a character, a multiple-character sequence that collates as if it were a single character, or a collating-sequence name for either) enclosed in [. and .]stands for the sequence of characters of that collating element. The sequence is treated as a single element of the bracket expression's list. This allows a bracket expression containing a multiple-character collating element to match more than one character, e.g., if the collating sequence includes a ch collating element, then the RE [[.ch.]]*c matches the first five characters of chchcc.

Note

PostgreSQL currently does not support multi-character collating elements. This information describes possible future behavior.

Within a bracket expression, a collating element enclosed in [= and =] is an equivalence class, standing for the sequences of characters of all collating elements equivalent to that one, including itself. (If there are no other equivalent collating elements, the treatment is as if the enclosing delimiters were [. and .].) For example, if o and ^ are the members of an equivalence class, then [[=o=]], [[=^=]], and [o^] are all synonymous. An equivalence class cannot be an endpoint of a range.

Within a bracket expression, the name of a character class enclosed in [: and :] stands for the list of all characters belonging to that class. Standard character class names are: alnum, alpha, blank,cntrl, digit, graph, lower, print, punct, space, upper, xdigit

There are two special cases of bracket expressions: the bracket expressions [[:<:]] and [[:>:]] are constraints, matching empty strings at the beginning and end of a word respectively. A word is defined as a sequence of word characters that is neither preceded nor followed by word characters. A word character is an alnum character (as defined by ctype) or an underscore. This is an extension, compatible with but not specified by POSIX 1003.2, and should be used with caution in software intended to be portable to other systems. The constraint escapes described below are usually preferable; they are no more standard, but are easier to type.

9.7.3.3. Regular Expression Escapes

Escapes are special sequences beginning with \ followed by an alphanumeric character. Escapes come in several varieties: character entry, class shorthands, constraint escapes, and back references. A \ followed by an alphanumeric character but not constituting a valid escape is illegal in AREs. In EREs, there are no escapes: outside a bracket expression, a \ followed by an alphanumeric character merely stands for that character as an ordinary character, and inside a bracket expression, \ is an ordinary character. (The latter is the one actual incompatibility between EREs and AREs.)

Character-entry escapes exist to make it easier to specify non-printing and other inconvenient characters in REs. They are shown in .

Class-shorthand escapes provide shorthands for certain commonly-used character classes. They are shown in .

A constraint escape is a constraint, matching the empty string if specific conditions are met, written as an escape. They are shown in .

A back reference (\n) matches the same string matched by the previous parenthesized subexpression specified by the number n (see ). For example, ([bc])\1 matches bb or cc but not bcor cb. The subexpression must entirely precede the back reference in the RE. Subexpressions are numbered in the order of their leading parentheses. Non-capturing parentheses do not define subexpressions.

Table 9.18. Regular Expression Character-entry Escapes

Hexadecimal digits are 0-9, a-f, and A-F. Octal digits are 0-7.

Numeric character-entry escapes specifying values outside the ASCII range (0-127) have meanings dependent on the database encoding. When the encoding is UTF-8, escape values are equivalent to Unicode code points, for example \u1234 means the character U+1234. For other multibyte encodings, character-entry escapes usually just specify the concatenation of the byte values for the character. If the escape value does not correspond to any legal character in the database encoding, no error will be raised, but it will never match any data.

The character-entry escapes are always taken as ordinary characters. For example, \135 is ] in ASCII, but \135 does not terminate a bracket expression.

Table 9.19. Regular Expression Class-shorthand Escapes

Within bracket expressions, \d, \s, and \w lose their outer brackets, and \D, \S, and \W are illegal. (So, for example, [a-c\d] is equivalent to [a-c[:digit:]]. Also, [a-c\D], which is equivalent to [a-c^[:digit:]], is illegal.)

Table 9.20. Regular Expression Constraint Escapes

A word is defined as in the specification of [[:<:]] and [[:>:]] above. Constraint escapes are illegal within bracket expressions.

Table 9.21. Regular Expression Back References

Note

There is an inherent ambiguity between octal character-entry escapes and back references, which is resolved by the following heuristics, as hinted at above. A leading zero always indicates an octal escape. A single non-zero digit, not followed by another digit, is always taken as a back reference. A multi-digit sequence not starting with a zero is taken as a back reference if it comes after a suitable subexpression (i.e., the number is in the legal range for a back reference), and otherwise is taken as octal.

9.7.3.4. Regular Expression Metasyntax

In addition to the main syntax described above, there are some special forms and miscellaneous syntactic facilities available.

An RE can begin with one of two special director prefixes. If an RE begins with ***:, the rest of the RE is taken as an ARE. (This normally has no effect in PostgreSQL, since REs are assumed to be AREs; but it does have an effect if ERE or BRE mode had been specified by the flags parameter to a regex function.) If an RE begins with ***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters.

An ARE can begin with embedded options: a sequence (?xyz) (where xyz is one or more alphabetic characters) specifies options affecting the rest of the RE. These options override any previously determined options — in particular, they can override the case-sensitivity behavior implied by a regex operator, or the flags parameter to a regex function. The available option letters are shown in . Note that these same option letters are used in the flags parameters of regex functions.

Table 9.22. ARE Embedded-option Letters

Embedded options take effect at the ) terminating the sequence. They can appear only at the start of an ARE (after the ***: director if any).

In addition to the usual (tight) RE syntax, in which all characters are significant, there is an expanded syntax, available by specifying the embedded x option. In the expanded syntax, white-space characters in the RE are ignored, as are all characters between a # and the following newline (or the end of the RE). This permits paragraphing and commenting a complex RE. There are three exceptions to that basic rule:

a white-space character or # preceded by \ is retained
white space or # within a bracket expression is retained
white space and comments cannot appear within multi-character symbols, such as (?:

For this purpose, white-space characters are blank, tab, newline, and any character that belongs to the space character class.

Finally, in an ARE, outside bracket expressions, the sequence (?#ttt) (where ttt is any text not containing a )) is a comment, completely ignored. Again, this is not allowed between the characters of multi-character symbols, like (?:. Such comments are more a historical artifact than a useful facility, and their use is deprecated; use the expanded syntax instead.

None of these metasyntax extensions is available if an initial ***= director has specified that the user's input be treated as a literal string rather than as an RE.

9.7.3.5. Regular Expression Matching Rules

In the event that an RE could match more than one substring of a given string, the RE matches the one starting earliest in the string. If the RE could match more than one substring starting at that point, either the longest possible match or the shortest possible match will be taken, depending on whether the RE is greedy or non-greedy.

Whether an RE is greedy or not is determined by the following rules:

Most atoms, and all constraints, have no greediness attribute (because they cannot match variable amounts of text anyway).
Adding parentheses around an RE does not change its greediness.
A quantified atom with a fixed-repetition quantifier ({m} or {

The above rules associate greediness attributes not only with individual quantified atoms, but with branches and entire REs that contain quantified atoms. What that means is that the matching is done in such a way that the branch, or whole RE, matches the longest or shortest possible substring as a whole. Once the length of the entire match is determined, the part of it that matches any particular subexpression is determined on the basis of the greediness attribute of that subexpression, with subexpressions starting earlier in the RE taking priority over ones starting later.

An example of what this means:

In the first case, the RE as a whole is greedy because Y* is greedy. It can match beginning at the Y, and it matches the longest possible string starting there, i.e., Y123. The output is the parenthesized part of that, or 123. In the second case, the RE as a whole is non-greedy because Y*? is non-greedy. It can match beginning at the Y, and it matches the shortest possible string starting there, i.e., Y1. The subexpression [0-9]{1,3} is greedy but it cannot change the decision as to the overall match length; so it is forced to match just 1.

In short, when an RE contains both greedy and non-greedy subexpressions, the total match length is either as long as possible or as short as possible, according to the attribute assigned to the whole RE. The attributes assigned to the subexpressions only affect how much of that match they are allowed to “eat” relative to each other.

The quantifiers {1,1} and {1,1}? can be used to force greediness or non-greediness, respectively, on a subexpression or a whole RE. This is useful when you need the whole RE to have a greediness attribute different from what's deduced from its elements. As an example, suppose that we are trying to separate a string containing some digits into the digits and the parts before and after them. We might try to do that like this:

That didn't work: the first .* is greedy so it “eats” as much as it can, leaving the \d+ to match at the last possible place, the last digit. We might try to fix that by making it non-greedy:

That didn't work either, because now the RE as a whole is non-greedy and so it ends the overall match as soon as possible. We can get what we want by forcing the RE as a whole to be greedy:

Controlling the RE's overall greediness separately from its components' greediness allows great flexibility in handling variable-length patterns.

When deciding what is a longer or shorter match, match lengths are measured in characters, not collating elements. An empty string is considered longer than no match at all. For example: bb*matches the three middle characters of abbbc; (week|wee)(night|knights) matches all ten characters of weeknights; when (.*).* is matched against abc the parenthesized subexpression matches all three characters; and when (a*)* is matched against bc both the whole RE and the parenthesized subexpression match an empty string.

If case-independent matching is specified, the effect is much as if all case distinctions had vanished from the alphabet. When an alphabetic that exists in multiple cases appears as an ordinary character outside a bracket expression, it is effectively transformed into a bracket expression containing both cases, e.g., x becomes [xX]. When it appears inside a bracket expression, all case counterparts of it are added to the bracket expression, e.g., [x] becomes [xX] and [^x] becomes [^xX].

If newline-sensitive matching is specified, . and bracket expressions using ^ will never match the newline character (so that matches will never cross newlines unless the RE explicitly arranges it) and ^and $ will match the empty string after and before a newline respectively, in addition to matching at beginning and end of string respectively. But the ARE escapes \A and \Z continue to match beginning or end of string only.

If partial newline-sensitive matching is specified, this affects . and bracket expressions as with newline-sensitive matching, but not ^ and $.

If inverse partial newline-sensitive matching is specified, this affects ^ and $ as with newline-sensitive matching, but not . and bracket expressions. This isn't very useful but is provided for symmetry.

9.7.3.6. Limits And Compatibility

No particular limit is imposed on the length of REs in this implementation. However, programs intended to be highly portable should not employ REs longer than 256 bytes, as a POSIX-compliant implementation can refuse to accept such REs.

The only feature of AREs that is actually incompatible with POSIX EREs is that \ does not lose its special significance inside bracket expressions. All other ARE features use syntax which is illegal or has undefined or unspecified effects in POSIX EREs; the *** syntax of directors likewise is outside the POSIX syntax for both BREs and EREs.

Many of the ARE extensions are borrowed from Perl, but some have been changed to clean them up, and a few Perl extensions are not present. Incompatibilities of note include \b, \B, the lack of special treatment for a trailing newline, the addition of complemented bracket expressions to the things affected by newline-sensitive matching, the restrictions on parentheses and back references in lookahead/lookbehind constraints, and the longest/shortest-match (rather than first-match) matching semantics.

Two significant incompatibilities exist between AREs and the ERE syntax recognized by pre-7.4 releases of PostgreSQL:

In AREs, \ followed by an alphanumeric character is either an escape or an error, while in previous releases, it was just another way of writing the alphanumeric. This should not be much of a problem because there was no reason to write such a sequence in earlier releases.
In AREs, \ remains a special character within [], so a literal \ within a bracket expression must be written \\.

9.7.3.7. Basic Regular Expressions

BREs differ from EREs in several respects. In BREs, |, +, and ? are ordinary characters and there is no equivalent for their functionality. The delimiters for bounds are \{ and \}, with { and } by themselves ordinary characters. The parentheses for nested subexpressions are $ and $, with ( and ) by themselves ordinary characters. ^ is an ordinary character except at the beginning of the RE or the beginning of a parenthesized subexpression, $

9.26. 系統管理函式

本節中描述的函數用於控制和監控 PostgreSQL 環境。

9.26.1. 組態設定函數

Table 9.77 列出了可用於查詢和變更執行時組態參數的函數。

Table 9.77. Configuration Settings Functions

函數 current_setting 會產生設定 setting_name 目前的值。它對應於 SQL 指令 SHOW。範例如下：

如果沒有名為 setting_name 的設定，則 current_setting 會拋出錯誤，除非有設定了 missing_ok，並且為 true。

set_config 將參數 setting_name 設定為 new_value。如果 is_local 為true，則新值僅適用於目前的交易事務。如果要將新值套用於目前連線之中，請改用 false。此函數對應於 SQL 命令 SET。範例如下：

9.26.2. Server Signaling Functions

The functions shown in send control signals to other server processes. Use of these functions is restricted to superusers by default but access may be granted to others using GRANT, with noted exceptions.

Table 9.78. Server Signaling Functions

Each of these functions returns true if successful and false otherwise.

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.

pg_reload_conf sends a SIGHUP signal to the server, causing configuration files to be reloaded by all server processes.

pg_rotate_logfile signals the log-file manager to switch to a new output file immediately. This works only when the built-in log collector is running, since otherwise there is no log-file manager subprocess.

9.26.3. Backup Control Functions

The functions shown in assist in making on-line backups. These functions cannot be executed during recovery (except pg_is_in_backup, pg_backup_start_time and pg_wal_lsn_diff).

Table 9.79. Backup Control Functions

pg_start_backup accepts an arbitrary user-defined label for the backup. (Typically this would be the name under which the backup dump file will be stored.) When used in exclusive mode, the function writes a backup label file (backup_label) and, if there are any links in the pg_tblspc/ directory, a tablespace map file (tablespace_map) into the database cluster's data directory, performs a checkpoint, and then returns the backup's starting write-ahead log location as text. The user can ignore this result value, but it is provided in case it is useful. When used in non-exclusive mode, the contents of these files are instead returned by the pg_stop_backup function, and should be written to the backup by the caller.

There is an optional second parameter of type boolean. If true, it specifies executing pg_start_backup as quickly as possible. This forces an immediate checkpoint which will cause a spike in I/O operations, slowing any concurrently executing queries.

In an exclusive backup, pg_stop_backup removes the label file and, if it exists, the tablespace_map file created by pg_start_backup. In a non-exclusive backup, the contents of the backup_labeland tablespace_map are returned in the result of the function, and should be written to files in the backup (and not in the data directory). There is an optional second parameter of type boolean. If false, the pg_stop_backup will return immediately after the backup is completed without waiting for WAL to be archived. This behavior is only useful for backup software which independently monitors WAL archiving. Otherwise, WAL required to make the backup consistent might be missing and make the backup useless. When this parameter is set to true, pg_stop_backup will wait for WAL to be archived when archiving is enabled; on the standby, this means that it will wait only when archive_mode = always. If write activity on the primary is low, it may be useful to run pg_switch_wal

When executed on a primary, the function also creates a backup history file in the write-ahead log archive area. The history file includes the label given to pg_start_backup, the starting and ending write-ahead log locations for the backup, and the starting and ending times of the backup. The return value is the backup's ending write-ahead log location (which again can be ignored). After recording the ending location, the current write-ahead log insertion point is automatically advanced to the next write-ahead log file, so that the ending write-ahead log file can be archived immediately to complete the backup.

pg_switch_wal moves to the next write-ahead log file, allowing the current file to be archived (assuming you are using continuous archiving). The return value is the ending write-ahead log location + 1 within the just-completed write-ahead log file. If there has been no write-ahead log activity since the last write-ahead log switch, pg_switch_wal does nothing and returns the start location of the write-ahead log file currently in use.

pg_create_restore_point creates a named write-ahead log record that can be used as recovery target, and returns the corresponding write-ahead log location. The given name can then be used with to specify the point up to which recovery will proceed. Avoid creating multiple restore points with the same name, since recovery will stop at the first one whose name matches the recovery target.

pg_current_wal_lsn displays the current write-ahead log write location in the same format used by the above functions. Similarly, pg_current_wal_insert_lsn displays the current write-ahead log insertion location and pg_current_wal_flush_lsn displays the current write-ahead log flush location. The insertion location is the “logical” end of the write-ahead log at any instant, while the write location is the end of what has actually been written out from the server's internal buffers and flush location is the location guaranteed to be written to durable storage. The write location is the end of what can be examined from outside the server, and is usually what you want if you are interested in archiving partially-complete write-ahead log files. The insertion and flush locations are made available primarily for server debugging purposes. These are both read-only operations and do not require superuser permissions.

You can use pg_walfile_name_offset to extract the corresponding write-ahead log file name and byte offset from the results of any of the above functions. For example:

Similarly, pg_walfile_name extracts just the write-ahead log file name. When the given write-ahead log location is exactly at a write-ahead log file boundary, both these functions return the name of the preceding write-ahead log file. This is usually the desired behavior for managing write-ahead log archiving behavior, since the preceding file is the last one that currently needs to be archived.

pg_wal_lsn_diff calculates the difference in bytes between two write-ahead log locations. It can be used with pg_stat_replication or some functions shown in to get the replication lag.

For details about proper usage of these functions, see .

9.26.4. Recovery Control Functions

The functions shown in provide information about the current status of the standby. These functions may be executed both during recovery and in normal running.

Table 9.80. Recovery Information Functions

The functions shown in control the progress of recovery. These functions may be executed only during recovery.

Table 9.81. Recovery Control Functions

While recovery is paused no further database changes are applied. If in hot standby, all new queries will see the same consistent snapshot of the database, and no further query conflicts will be generated until recovery is resumed.

If streaming replication is disabled, the paused state may continue indefinitely without problem. While streaming replication is in progress WAL records will continue to be received, which will eventually fill available disk space, depending upon the duration of the pause, the rate of WAL generation and available disk space.

9.26.5. Snapshot Synchronization Functions

PostgreSQL allows database sessions to synchronize their snapshots. A snapshot determines which data is visible to the transaction that is using the snapshot. Synchronized snapshots are necessary when two or more sessions need to see identical content in the database. If two sessions just start their transactions independently, there is always a possibility that some third transaction commits between the executions of the two START TRANSACTION commands, so that one session sees the effects of that transaction and the other does not.

To solve this problem, PostgreSQL allows a transaction to export the snapshot it is using. As long as the exporting transaction remains open, other transactions can import its snapshot, and thereby be guaranteed that they see exactly the same view of the database that the first transaction sees. But note that any database changes made by any one of these transactions remain invisible to the other transactions, as is usual for changes made by uncommitted transactions. So the transactions are synchronized with respect to pre-existing data, but act normally for changes they make themselves.

Snapshots are exported with the pg_export_snapshot function, shown in , and imported with the command.

Table 9.82. Snapshot Synchronization Functions

The function pg_export_snapshot saves the current snapshot and returns a text string identifying the snapshot. This string must be passed (outside the database) to clients that want to import the snapshot. The snapshot is available for import only until the end of the transaction that exported it. A transaction can export more than one snapshot, if needed. Note that doing so is only useful in READ COMMITTED transactions, since in REPEATABLE READ and higher isolation levels, transactions use the same snapshot throughout their lifetime. Once a transaction has exported any snapshots, it cannot be prepared with .

See for details of how to use an exported snapshot.

9.26.6. Replication Functions

The functions shown in are for controlling and interacting with replication features. See , , and for information about the underlying features. Use of these functions is restricted to superusers.

Many of these functions have equivalent commands in the replication protocol; see .

The functions described in , , and are also relevant for replication.

Table 9.83. Replication SQL Functions

9.26.7. Database Object Management Functions

The functions shown in calculate the disk space usage of database objects.

Table 9.84. Database Object Size Functions

pg_column_size shows the space used to store any individual data value.

pg_total_relation_size accepts the OID or name of a table or toast table, and returns the total on-disk space used for that table, including all associated indexes. This function is equivalent to pg_table_size + pg_indexes_size.

pg_table_size accepts the OID or name of a table and returns the disk space needed for that table, exclusive of indexes. (TOAST space, free space map, and visibility map are included.)

pg_indexes_size accepts the OID or name of a table and returns the total disk space used by all the indexes attached to that table.

pg_database_size and pg_tablespace_size accept the OID or name of a database or tablespace, and return the total disk space used therein. To use pg_database_size, you must have CONNECTpermission on the specified database (which is granted by default), or be a member of the pg_read_all_stats role. To use pg_tablespace_size, you must have CREATE permission on the specified tablespace, or be a member of the pg_read_all_stats role unless it is the default tablespace for the current database.

pg_relation_size accepts the OID or name of a table, index or toast table, and returns the on-disk size in bytes of one fork of that relation. (Note that for most purposes it is more convenient to use the higher-level functions pg_total_relation_size or pg_table_size, which sum the sizes of all forks.) With one argument, it returns the size of the main data fork of the relation. The second argument can be provided to specify which fork to examine:

'main' returns the size of the main data fork of the relation.
'fsm' returns the size of the Free Space Map (see ) associated with the relation.
'vm' returns the size of the Visibility Map (see ) associated with the relation.

pg_size_pretty can be used to format the result of one of the other functions in a human-readable way, using bytes, kB, MB, GB or TB as appropriate.

pg_size_bytes can be used to get the size in bytes from a string in human-readable format. The input may have units of bytes, kB, MB, GB or TB, and is parsed case-insensitively. If no units are specified, bytes are assumed.

Note

The units kB, MB, GB and TB used by the functions pg_size_pretty and pg_size_bytes are defined using powers of 2 rather than powers of 10, so 1kB is 1024 bytes, 1MB is 10242 = 1048576 bytes, and so on.

The functions above that operate on tables or indexes accept a regclass argument, which is simply the OID of the table or index in the pg_class system catalog. You do not have to look up the OID by hand, however, since the regclass data type's input converter will do the work for you. Just write the table name enclosed in single quotes so that it looks like a literal constant. For compatibility with the handling of ordinary SQL names, the string will be converted to lower case unless it contains double quotes around the table name.

If an OID that does not represent an existing object is passed as argument to one of the above functions, NULL is returned.

The functions shown in assist in identifying the specific disk files associated with database objects.

Table 9.85. Database Object Location Functions

pg_relation_filenode accepts the OID or name of a table, index, sequence, or toast table, and returns the “filenode” number currently assigned to it. The filenode is the base component of the file name(s) used for the relation (see for more information). For most tables the result is the same as pg_class.relfilenode, but for certain system catalogs relfilenode is zero and this function must be used to get the correct value. The function returns NULL if passed a relation that does not have storage, such as a view.

pg_relation_filepath is similar to pg_relation_filenode, but it returns the entire file path name (relative to the database cluster's data directory PGDATA) of the relation.

pg_filenode_relation is the reverse of pg_relation_filenode. Given a “tablespace” OID and a “filenode”, it returns the associated relation's OID. For a table in the database's default tablespace, the tablespace can be specified as 0.

lists functions used to manage collations.

Table 9.86. Collation Management Functions

pg_collation_actual_version returns the actual version of the collation object as it is currently installed in the operating system. If this is different from the value in pg_collation.collversion, then objects depending on the collation might need to be rebuilt. See also .

pg_import_system_collations adds collations to the system catalog pg_collation based on all the locales it finds in the operating system. This is what initdb uses; see for more details. If additional locales are installed into the operating system later on, this function can be run again to add collations for the new locales. Locales that match existing entries in pg_collation will be skipped. (But collation objects based on locales that are no longer present in the operating system are not removed by this function.) The schema parameter would typically be pg_catalog, but that is not a requirement; the collations could be installed into some other schema as well. The function returns the number of new collation objects it created.

9.26.8. Index Maintenance Functions

shows the functions available for index maintenance tasks. These functions cannot be executed during recovery. Use of these functions is restricted to superusers and the owner of the given index.

Table 9.87. Index Maintenance Functions

brin_summarize_new_values accepts the OID or name of a BRIN index and inspects the index to find page ranges in the base table that are not currently summarized by the index; for any such range it creates a new summary index tuple by scanning the table pages. It returns the number of new page range summaries that were inserted into the index. brin_summarize_range does the same, except it only summarizes the range that covers the given block number.

gin_clean_pending_list accepts the OID or name of a GIN index and cleans up the pending list of the specified index by moving entries in it to the main GIN data structure in bulk. It returns the number of pages removed from the pending list. Note that if the argument is a GIN index built with the fastupdate option disabled, no cleanup happens and the return value is 0, because the index doesn't have a pending list. Please see and for details of the pending list and fastupdate option.

9.26.9. Generic File Access Functions

The functions shown in provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed unless the user is granted the role pg_read_server_files. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files.

Note that granting users the EXECUTE privilege on the pg_read_file(), or related, functions allows them the ability to read any file on the server which the database can read and that those reads bypass all in-database privilege checks. This means that, among other things, a user with this access is able to read the contents of the pg_authid table where authentication information is contained, as well as read any file in the database. Therefore, granting access to these functions should be carefully considered.

Table 9.88. Generic File Access Functions

Some of these functions take an optional missing_ok parameter, which specifies the behavior when the file or directory does not exist. If true, the function returns NULL (except pg_ls_dir, which returns an empty result set). If false, an error is raised. The default is false.

pg_ls_dir returns the names of all files (and directories and other special files) in the specified directory. The include_dot_dirs indicates whether “.” and “..” are included in the result set. The default is to exclude them (false), but including them can be useful when missing_ok is true, to distinguish an empty directory from an non-existent directory.

pg_ls_logdir returns the name, size, and last modified time (mtime) of each file in the log directory. By default, only superusers and members of the pg_monitor role can use this function. Access may be granted to others using GRANT.

pg_ls_waldir returns the name, size, and last modified time (mtime) of each file in the write ahead log (WAL) directory. By default only superusers and members of the pg_monitor role can use this function. Access may be granted to others using GRANT.

pg_read_file returns part of a text file, starting at the given offset, returning at most length bytes (less if the end of file is reached first). If offset is negative, it is relative to the end of the file. If offset and length are omitted, the entire file is returned. The bytes read from the file are interpreted as a string in the server encoding; an error is thrown if they are not valid in that encoding.

pg_read_binary_file is similar to pg_read_file, except that the result is a bytea value; accordingly, no encoding checks are performed. In combination with the convert_from function, this function can be used to read a file in a specified encoding:

pg_stat_file returns a record containing the file size, last accessed time stamp, last modified time stamp, last file status change time stamp (Unix platforms only), file creation time stamp (Windows only), and a boolean indicating if it is a directory. Typical usages include:

9.26.10. Advisory Lock Functions

The functions shown in manage advisory locks. For details about proper use of these functions, see .

Table 9.89. Advisory Lock Functions

pg_advisory_lock locks an application-defined resource, which can be identified either by a single 64-bit key value or two 32-bit key values (note that these two key spaces do not overlap). If another session already holds a lock on the same resource identifier, this function will wait until the resource becomes available. The lock is exclusive. Multiple lock requests stack, so that if the same resource is locked three times it must then be unlocked three times to be released for other sessions' use.

pg_advisory_lock_shared works the same as pg_advisory_lock, except the lock can be shared with other sessions requesting shared locks. Only would-be exclusive lockers are locked out.

pg_try_advisory_lock is similar to pg_advisory_lock, except the function will not wait for the lock to become available. It will either obtain the lock immediately and return true, or return falseif the lock cannot be acquired immediately.

pg_try_advisory_lock_shared works the same as pg_try_advisory_lock, except it attempts to acquire a shared rather than an exclusive lock.

pg_advisory_unlock will release a previously-acquired exclusive session level advisory lock. It returns true if the lock is successfully released. If the lock was not held, it will return false, and in addition, an SQL warning will be reported by the server.

pg_advisory_unlock_shared works the same as pg_advisory_unlock, except it releases a shared session level advisory lock.

pg_advisory_unlock_all will release all session level advisory locks held by the current session. (This function is implicitly invoked at session end, even if the client disconnects ungracefully.)

pg_advisory_xact_lock works the same as pg_advisory_lock, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.

pg_advisory_xact_lock_shared works the same as pg_advisory_lock_shared, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.

pg_try_advisory_xact_lock works the same as pg_try_advisory_lock, except the lock, if acquired, is automatically released at the end of the current transaction and cannot be released explicitly.

pg_try_advisory_xact_lock_shared works the same as pg_try_advisory_lock_shared, except the lock, if acquired, is automatically released at the end of the current transaction and cannot be released explicitly.

9. 函式及運算子

9.1. 邏輯運算子

9.2. 比較函式及運算子

Note

Tip

9.3. 數學函式及運算子

9.6. 二元字串函式及運算子

Note

9.10. 列舉型別函式

9.16. 序列函式

Note

Important

Important

9.17. 條件表示式

提示

9.22. 子查詢

9.22.1. EXISTS

9.23. 資料列與陣列的比較運算

9.23.1. IN

9.23.2. NOT IN

小技巧

9.23.3. ANY/SOME (array)

9.23.4. ALL (array)

9.23.5. 資料列行建構函數比較

9.23.6. 複合型別比較

9.27. 觸發函式

9. 函式及運算子

9.1. 邏輯運算子

9.2. 比較函式及運算子

Note

Tip

9.10. 列舉型別函式

9.27. 觸發函式

9.16. 序列函式

Note

Important

Important

9.6. 二元字串函式及運算子

Note

9.23. 資料列與陣列的比較運算

9.23.1. IN

9.23.2. NOT IN

小技巧

9.23.3. ANY/SOME (array)

9.23.4. ALL (array)

9.23.5. 資料列行建構函數比較

9.23.6. 複合型別比較

9.3. 數學函式及運算子

9.22. 子查詢

9.22.1. EXISTS

9.17. 條件表示式

提示

9.22.2. IN

9.22.3. NOT IN

9.22.4. ANY/SOME

9.22.5. ALL

9.22.6. Single-row Comparison

注意

9.17.2. COALESCE

9.17.3. NULLIF

9.17.4. GREATEST and LEAST

Note

9.21. Window函式

Note

9.28. 事件觸發函式

9.28.1. Capturing Changes at Command End

9.28.2. Processing Objects Dropped by a DDL Command

9.28.3. Handling a Table Rewrite Event

9.5. 位元字串函式及運算子

Note

9.24. 集合回傳函式

9.19. 範圍函式及運算子

9.12. 網路位址函式及運算子

9.18. 陣列函式及運算子

Note

9.15. JSON函式及運算子

Note

Note

Note

Note

9.22.1. `EXISTS`

9.23.1. `IN`

9.23.2. `NOT IN`

9.23.3. `ANY`/`SOME` (array)

9.23.4. `ALL` (array)

9.23.1. `IN`

9.23.2. `NOT IN`

9.23.3. `ANY`/`SOME` (array)

9.23.4. `ALL` (array)

9.22.1. `EXISTS`

9.22.2. `IN`

9.22.3. `NOT IN`

9.22.4. `ANY`/`SOME`

9.22.5. `ALL`

9.17.2. `COALESCE`

9.17.3. `NULLIF`

9.17.4. `GREATEST` and `LEAST`

9.9.1. `EXTRACT`, `date_part`

9.9.2. `date_trunc`

9.7.1. `LIKE`

9.7.2. `SIMILAR TO` Regular Expressions