暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / postgresql官方文档之Functions and Operators

postgresql官方文档之Functions and Operators

原创 _ All China Database Union 2024-04-15
896

9.1. Logical Operators

SQL中可用的逻辑运算符，包括：

  • boolean AND boolean → boolean
  • boolean OR boolean → boolean
  • NOT boolean → boolean
    此外，还提到了SQL使用的是一个三值逻辑系统，包含 true、false 和 null（代表“未知”）。并给出了这些逻辑运算符的真值表，展示了不同输入值下运算符的结果。特别指出，AND 和 OR 运算符是可交换的，即左右操作数交换不会影响结果。但是，并没有保证左操作数一定在右操作数之前被评估。

9.2. Comparison Functions and Operators

SQL中可用的比较函数和运算符。这些包括：

  • 常用的比较运算符，如 < (小于), > (大于), <= (小于等于), >= (大于等于), = (等于), <> 或 != (不等于)。这些运算符都是二元运算符，返回布尔类型的结果。值得注意的是，<> 是标准SQL表示“不等于”的符号，而 != 是一个别名，会在解析的早期阶段被转换为 <>。因此，不能实现 != 和 <> 运算符做不同的事情。
  • 比较谓词，这些谓词的行为类似于运算符，但具有SQL标准规定的特殊语法。
    这些比较运算符适用于所有具有自然排序的内置数据类型，包括数字类型、字符串类型和日期/时间类型。此外，如果其组件数据类型可比较，数组、复合类型和范围也可以进行比较。
    通常也可以比较相关数据类型的值；例如，integer > bigint 是可行的。如果没有直接可用的“跨类型”比较运算符，解析器会将较不通用的类型强制转换为更通用的类型，并应用后者的比较运算符。

9.3. Mathematical Functions and Operators

在PostgreSQL 中用于处理数值数据类型的数学函数和运算符。这包括对标准数字类型（例如 smallint, integer, bigint, numeric, real, 和 double precision）的支持，以及一些特定的运算符和函数。以下是一些主要的数学运算符和它们的用法：

  • + (加法): numeric_type + numeric_type → numeric_type，例如 2 + 3 → 5

  • - (减法): numeric_type - numeric_type → numeric_type，例如 2 - 3 → -1

  • * (乘法): numeric_type * numeric_type → numeric_type，例如 2 * 3 → 6

  • / (除法): numeric_type / numeric_type → numeric_type，对于整数类型，除法会将结果截断为零方向，例如 5.0 / 2 → 2.5 和 5 / 2 → 2

  • % (模运算): numeric_type % numeric_type → numeric_type，仅对 smallint, integer, bigint, 和 numeric 类型可用

  • ^ (指数运算): numeric ^ numeric → numeric 和 double precision ^ double precision → double precision，例如 2 ^ 3 → 8

  • |/ (平方根): |/ double precision → double precision，例如 |/ 25.0 → 5

  • ||/ (立方根): ||/ double precision → double precision，例如 ||/ 64.0 → 4

  • @ (绝对值): @ numeric_type → numeric_type，例如 @ -5.0 → 5.0

  • 位运算符：包括 & (位与), | (位或), # (位异或), ~ (位非)，适用于整数类型

这些运算符和函数允许对数字进行各种数学计算，支持基本的算术运算以及更复杂的操作，如指数运算、平方根和立方根计算等。除了上述列出的运算符外，PostgreSQL 还提供了一系列数学函数，用于处理更复杂的数学问题。

9.4. String Functions and Operators

PostgreSQL 中用于检查和操作字符串值的函数和运算符。这些字符串值包括字符、字符变量和文本类型。以下是一些主要的字符串函数和运算符及其用法：

  • text ^@ text → boolean: 如果第一个字符串以第二个字符串开头，则返回 true。等同于 starts_with() 函数。例如，alphabet ^@ alph → t。
  • ascii(text) → integer: 返回参数中第一个字符的数值编码。在 UTF8 编码中，返回 Unicode 码点。例如，ascii(x) → 120。
  • substring(stringtext FROM patterntext) → text: 提取与 POSIX 正则表达式匹配的第一个子字符串。例如，substring(Thomas from ...$) → mas。
  • trim([LEADING|TRAILING|BOTH][characterstext] FROM stringtext) → text: 从字符串的开始、结束或两端移除指定的字符集合（默认为空格）。例如，trim(both xyz from yxTomxx) → Tom。
  • upper(text) → text: 将字符串转换为全部大写。例如，upper(tom) → TOM。
    这些函数和运算符声明为接受并返回文本类型，但它们可以交换地接受字符变量参数。字符类型的值会在应用函数或运算符之前转换为文本类型，这会去除字符值中的任何尾随空格。
    此外，还有更多的字符串操作函数和运算符可用，包括用于模式匹配和全文搜索的操作符。这些工具为处理和分析字符串数据提供了强大的能力，使得在数据库操作中可以执行复杂的字符串处理任务。

9.5. Binary String Functions and Operators

PostgreSQL 中用于检查和操作二进制字符串（即 bytea 类型）的函数和运算符。这些功能允许用户以二进制格式处理数据，适用于那些需要存储或操作原始字节数据的场景，例如文件内容、加密数据等。以下是一些主要的二进制字符串函数和运算符及其用法：

  • bytea || bytea → bytea: 连接两个二进制字符串。例如，\x123456::bytea || '\x789a00bcde::bytea → \x123456789a00bcde。
  • bit_length(bytea) → integer: 返回二进制字符串中的位数（字节数乘以8）。例如，bit_length('\x123456::bytea) → 24。
  • btrim(bytea, bytes bytea) → bytea: 移除二进制字符串开头和结尾的特定字节。例如，btrim('\x1234567890::bytea, '\x9012::bytea) → \x345678。
  • ltrim(bytea, bytes bytea) → bytea: 移除二进制字符串开头的特定字节。例如，ltrim('\x1234567890::bytea, '\x9012::bytea) → \x34567890。
  • octet_length(bytea) → integer: 返回二进制字符串的字节数。例如，octet_length('\x123456::bytea) → 3。
    这些函数和运算符为二进制数据的处理提供了强大的支持，使得用户可以在数据库中以灵活的方式处理和存储二进制数据。

9.6. Bit String Functions and Operators

PostgreSQL 中用于检查和操作位字符串（即 bit 和 bit varying 类型）的函数和运算符。位字符串是一种数据类型，用于存储位（bit）序列，这在需要精确控制位级数据时非常有用，如权限控制、特征标记等场景。以下是一些主要的位字符串函数和运算符及其用法：

  • bit || bit → bit: 位串连接。例如，B10001 || B011 → 10001011。
  • bit & bit → bit: 位串的按位与（AND）运算（输入必须等长）。例如，B10001 & B01101 → 00001。
  • bit | bit → bit: 位串的按位或（OR）运算（输入必须等长）。例如，B10001 | B01101 → 11101。
  • bit # bit → bit: 位串的按位异或（XOR）运算（输入必须等长）。例如，B10001 # B01101 → 11100。
  • ~bit → bit: 位串的按位取反（NOT）。例如，~ B10001 → 01110。
  • bit << integer → bit: 位串左移。例如，B10001 << 3 → 01000。
  • bit >> integer → bit: 位串右移。例如，B10001 >> 2 → 00100。
    此外，还介绍了一些特定的位串处理函数，如：
  • bit_count(bit) → bigint: 返回位串中设置为 1 的位的数量。例如，bit_count(B10111) → 4。
  • bit_length(bit) → integer: 返回位串的长度（位数）。例如，bit_length(B10111) → 5。
  • octet_length(bit) → integer: 返回位串的字节长度。例如，octet_length(B1011111011) → 2。
    这些运算符和函数为位串的处理提供了丰富的工具，使得用户可以在数据库中执行复杂的位级操作。

9.7. Pattern Matching

PostgreSQL 中提供的模式匹配功能，这些功能允许用户在字符串中搜索和匹配特定的模式。PostgreSQL 提供了三种不同的模式匹配方法：使用 SQL 标准的 LIKE 操作符、使用 SIMILAR TO 操作符（在 SQL:1999 中添加），以及使用 POSIX 风格的正则表达式。以下是这些方法的概述：

  1. LIKE 操作符：
    • LIKE 用于简单的模式匹配，其中 % 代表任意字符序列，而 _ 代表任意单个字符。例如，hello LIKE he% 返回 true。
    • LIKE 模式匹配总是覆盖整个字符串。若要匹配字符串中的序列，则模式必须以 % 开始和结束。
    • 为了匹配字面上的 % 或 _，需要使用转义字符（默认是 \）。
  2. SIMILAR TO 操作符：
    • SIMILAR TO 提供了类似于正则表达式的模式匹配，但语法更接近 SQL 标准。它支持 % 和 _，以及正则表达式中的量词如 *、+ 和 ?。
    • 与 LIKE 类似，SIMILAR TO 也是匹配整个字符串。要在字符串中匹配序列，模式同样需要以 % 开始和结束。
  3. POSIX 正则表达式：
    • PostgreSQL 支持强大的 POSIX 风格正则表达式，提供了比 LIKE 和 SIMILAR TO 更复杂的匹配功能。正则表达式允许在字符串中执行复杂的搜索和替换操作。
    • 正则表达式使用特殊的操作符，如 ~（区分大小写匹配）、~*（不区分大小写匹配）、!~（不匹配）和 !~*（不匹配，不区分大小写）。
    • 正则表达式支持各种特殊字符和序列，如 .（匹配任意单个字符）、*（匹配前一个字符零次或多次）、+（匹配前一个字符一次或多次）等。
      此外，PostgreSQL 提供了一系列与正则表达式相关的函数，如 substring、regexp_replace 和 regexp_matches 等，这些函数可以用于更复杂的模式匹配和数据提取任务。

9.8. Data Type Formatting Functions

PostgreSQL 中提供的一组函数，这些函数用于将各种数据类型（如日期/时间、整数、浮点数、数值等）格式化为字符串，或将格式化的字符串转换为特定的数据类型。这些功能强大的工具允许开发者以灵活的方式显示数据，或解析字符串数据为数据库中的特定类型。以下是一些主要的数据类型格式化函数及其用法：

  1. to_char(value, format)：将数字或日期/时间值转换为格式化的字符串。format 参数定义了输出或输入格式。例如，to_char(current_timestamp, YYYY-MM-DD HH24:MI:SS) 可以将当前时间戳格式化为指定的日期时间格式字符串。
  2. to_date(text, format)：将字符串转换为日期。format 参数指定了输入字符串的格式。例如，to_date(2023-04-01, YYYY-MM-DD) 将字符串转换为日期类型。
  3. to_number(text, format)：将格式化的数字字符串转换为数值类型。format 参数定义了输入字符串的格式。例如，to_number(1,234.56, 9,999.99) 将字符串转换为数值类型。
  4. to_timestamp(text, format)：将字符串转换为时间戳（带时区）。format 参数指定了输入字符串的格式。例如，to_timestamp(April 1, 2023, 12:00:00, Month DD, YYYY, HH24:MI:SS) 将字符串转换为时间戳类型。
    这些函数遵循一个通用的调用约定：第一个参数是要被格式化的值，第二个参数是一个模板，定义了输出或输入的格式。格式化模板包含预定义的模式字符，这些字符在格式化过程中被替换为相应的值。例如，YYYY 表示四位数的年份，MM 表示月份，DD 表示天数等。
    此外，还有一些特定的格式化模式字符，用于处理数字和日期/时间值的不同方面，如小数点、千位分隔符、月份名称、星期几名称等。

9.9. Date/Time Functions and Operators

PostgreSQL 中用于处理日期和时间值的函数和运算符。这些功能允许用户执行日期和时间的计算、格式化、提取特定部分等操作。以下是一些主要的日期/时间函数和运算符及其用法：

  1. 日期/时间运算符：
    • date + integer：给日期加上一定数量的天。例如，date 2001-09-28 + 7 结果为 2001-10-05。
    • date - integer：从日期减去一定数量的天。
    • timestamp + interval：给时间戳加上一个时间间隔。例如，timestamp 2001-09-28 01:00 + interval 23 hours 结果为 2001-09-29 00:00:00。
    • timestamp - interval：从时间戳减去一个时间间隔。
  2. 日期/时间函数：
    • age(timestamp, timestamp)：计算两个时间戳之间的年龄，返回一个表示年、月、日的间隔。例如，age(timestamp 2001-04-10, timestamp 1957-06-13) 结果为 43 years 9 mons 27 days。
    • current_date：返回当前日期。
    • current_time：返回当前时间（带时区）。
    • current_timestamp：返回当前时间戳（带时区）。
    • date_trunc(field, timestamp)：将时间戳截断到指定的部分。例如，date_trunc(hour, timestamp 2001-02-16 20:38:40) 结果为 2001-02-16 20:00:00。
    • extract(field from timestamp)：从时间戳中提取指定的部分（如年、月、日、小时等）。例如，extract(year from timestamp 2001-02-16 20:38:40) 结果为 2001。
  3. 特殊日期/时间处理函数：
    • to_timestamp(double precision)：将 Unix 时间戳（自 1970-01-01 00:00:00 UTC 以来的秒数）转换为时间戳（带时区）。
    • now()：返回当前事务的开始时间（时间戳，带时区）。
      日期/时间函数和运算符为数据库中的日期和时间处理提供了强大而灵活的工具，使得用户可以轻松地进行日期和时间的计算、格式化和转换等操作。

9.10. Enum Support Functions

PostgreSQL 中用于支持枚举类型（enum）的函数。枚举类型是一种数据类型，它由一组静态、有序的值组成。这些函数使得在不直接引用枚举值的硬编码字符串的情况下，能够更灵活地编程和查询枚举类型。以下是一些主要的枚举支持函数及其用法：

  1. enum_first(anyenum) → anyenum: 返回输入枚举类型的第一个值。例如，如果有一个名为 rainbow 的枚举类型，包含值 red, orange, yellow, green, blue, purple，则 enum_first(null::rainbow) 返回 red。
  2. enum_last(anyenum) → anyenum: 返回输入枚举类型的最后一个值。例如，enum_last(null::rainbow) 返回 purple。
  3. enum_range(anyenum) → anyarray: 返回一个数组，包含输入枚举类型的所有值，按照枚举中定义的顺序排列。例如，enum_range(null::rainbow) 返回数组 {red, orange, yellow, green, blue, purple}。
  4. enum_range(anyenum, anyenum) → anyarray: 返回一个数组，包含从第一个给定的枚举值到第二个给定的枚举值之间的所有枚举值（包括这两个值），按照枚举中定义的顺序排列。如果第一个参数是 null，则数组从枚举的第一个值开始；如果第二个参数是 null，则数组一直到枚举的最后一个值结束。例如，enum_range(orange::rainbow, blue::rainbow) 返回数组 {orange, yellow, green, blue}。

9.11. Geometric Functions and Operators

PostgreSQL 中用于处理几何数据类型的函数和运算符。几何类型包括点（point）、线段（lseg）、路径（path）、多边形（polygon）、圆（circle）等，这些类型支持创建和操作二维几何图形。以下是一些主要的几何运算符、函数及其用法：

  1. 几何运算符：
    • +：将点的坐标加到几何类型的每个点上，实现平移。例如，box '((1,1),(0,0)) + point '(2,0) → box '((3,1),(2,0))。
    • -：从几何类型的每个点上减去点的坐标，也是平移。例如，box '((1,1),(0,0)) - point '(2,0) → box ((-1,1),(-2,0))。
    • *：将几何类型的每个点与点进行乘法操作，可以实现旋转和缩放。例如，path '((0,0),(1,0)) * point '(0,1) → path '((0,0),(0,1))。
    • /：除法操作，与乘法相反，用于旋转和缩放。
    • @-@：计算几何对象的长度。例如，@-@ path [(0,0),(1,0),(1,1)] → 2。
    • #：计算两个几何对象的交点或点数。
  2. 几何函数：
    • area(geometric_type) → double precision：计算几何对象的面积。对于不封闭的路径，返回 NULL。
    • center(geometric_type) → point：计算几何对象的中心点。
    • diameter(circle) → double precision：计算圆的直径。
    • radius(circle) → double precision：计算圆的半径。
    • isclosed(path) → boolean：判断路径是否封闭。
    • isopen(path) → boolean：判断路径是否开放。
    • npoints(geometric_type) → integer：返回几何对象的点数。
    • width(box) → double precision、height(box) → double precision：计算盒子的宽度和高度。
      几何函数和运算符提供了强大的工具集，用于在 PostgreSQL 数据库中创建、查询和操作二维几何图形。这些功能特别适用于需要处理空间数据的应用，如地图服务、空间分析等。

9.12. Network Address Functions and Operators

PostgreSQL 中用于处理网络地址数据类型的函数和运算符。这些数据类型包括 cidr 和 inet，分别用于表示无类别域间路由（CIDR）块和具有可选子网掩码的互联网地址。以下是一些主要的网络地址运算符、函数及其用法：

  1. 网络地址运算符：
    • <<：子网包含运算符，检查左侧的地址是否是右侧地址的严格子网。例如，inet 192.168.1.5 << inet 192.168.1/24 → t。
    • <<=：子网或等于运算符，检查左侧的地址是否是右侧地址的子网或者与之相等。例如，inet 192.168.1/24 <<= inet 192.168.1/24 → t。
    • >> 和 >>=：这两个运算符与 << 和 <<= 相对应，但方向相反，用于检查右侧的地址是否是左侧地址的子网或等于左侧地址。
    • &&：运算符检查两个网络地址是否有重叠部分。例如，inet 192.168.1/24 && inet 192.168.1.80/28 → t。
  2. 网络地址函数：
    • broadcast(inet)：计算给定地址的广播地址。例如，broadcast(inet 192.168.1.5/24) → 192.168.1.255/24。
    • family(inet)：返回地址的 IP 版本（IPv4 或 IPv6）。例如，family(inet '::1) → 6。
    • host(inet)：返回地址的主机部分，忽略子网掩码。例如，host(inet 192.168.1.0/24) → 192.168.1.0。
    • masklen(inet)：返回地址的子网掩码长度。例如，masklen(inet 192.168.1.5/24) → 24。
    • netmask(inet)：计算地址的网络掩码。例如，netmask(inet 192.168.1.5/24) → 255.255.255.0。
    • network(inet)：返回地址的网络部分，将主机部分置零。例如，network(inet 192.168.1.5/24) → 192.168.1.0/24。
      这些运算符和函数为网络地址的存储、查询和操作提供了强大的支持，使得用户可以轻松地在数据库中进行复杂的网络计算和分析。

9.13. Text Search Functions and Operators

PostgreSQL 中用于全文搜索的功能，包括一系列函数和运算符，这些工具支持对文本数据进行高级搜索和匹配操作。全文搜索功能允许对大量文本进行搜索，找到包含指定词汇的文档，并且支持词汇的变形搜索、同义词处理、排除无关词（如“和”、“或”等）等高级特性。以下是一些主要的全文搜索函数和运算符及其用法：

  1. 全文搜索运算符：
    • @@：文本搜索匹配运算符，检查 tsvector（文本搜索向量）是否匹配 tsquery（文本搜索查询）。例如，to_tsvector(english, The quick brown fox) @@ to_tsquery(english, quick & fox) → t，表示文本向量与查询条件匹配。
    • @>：检查左侧的 tsvector 是否包含右侧的 tsvector 中的所有词汇。例如，a:1 b:2::tsvector @> b:2::tsvector → t。
    • <@：检查左侧的 tsvector 是否被右侧的 tsvector 完全包含。
  2. 全文搜索函数：
    • to_tsvector([config regconfig,] text) → tsvector：将文本转换为 tsvector，可选地指定文本搜索配置（如语言）。例如，to_tsvector(english, The quick brown fox) → brown:3 fox:4 quick:2。
    • to_tsquery([config regconfig,] text) → tsquery：将文本转换为 tsquery，可选地指定文本搜索配置。例如，to_tsquery(english, quick & fox) → quick & fox。
    • plainto_tsquery([config regconfig,] text) → tsquery：将简单文本查询转换为 tsquery，自动处理逻辑运算符。例如，plainto_tsquery(english, quick fox) → quick & fox。
    • phraseto_tsquery([config regconfig,] text) → tsquery：将文本转换为 tsquery，保留短语搜索的顺序。例如，phraseto_tsquery(english, quick brown fox) → quick <-> brown <-> fox。
    • ts_rank([weights float4[],] vector tsvector, query tsquery [, normalization integer ]) → float4：根据 tsquery 计算 tsvector 的相关性得分。例如，ts_rank(to_tsvector(english, The quick brown fox), to_tsquery(english, quick | fox)) → 0.6。
      全文搜索功能提供了对文本数据的强大搜索能力，适用于需要对大量文本进行内容搜索和分析的应用场景。

9.14. UUID Functions

PostgreSQL 中用于生成和处理通用唯一识别码（UUID）的函数。UUID 是一种标准化的唯一标识符，广泛用于数据库和应用程序中，以确保每个实体的唯一性，即使在大规模分布式系统中也是如此。以下是 PostgreSQL 中提供的 UUID 函数及其用法：

  1. gen_random_uuid() → uuid：

    • 这个函数生成一个版本4（随机）UUID。这是最常用的 UUID 类型，适用于大多数应用场合。例如，调用 gen_random_uuid() 可能会返回 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11。

  2. 其他 UUID 函数：

    • PostgreSQL 的 uuid-ossp 模块提供了额外的函数来生成不同版本的 UUID（如版本1、版本3、版本5），这些版本的 UUID 基于时间戳、名称空间、MD5 或 SHA-1 散列等不同的生成策略。
    • 要使用 uuid-ossp 模块提供的函数，需要先在数据库中启用该模块，通常通过执行 CREATE EXTENSION "uuid-ossp"; 命令。
      除了生成 UUID 外，PostgreSQL 还提供了用于比较 UUID 值的标准比较运算符，如等于（=）、不等于（<>）、小于（<）、大于（>）等。
      UUID 函数提供了一种生成全局唯一标识符的强大方法，非常适合用作数据库记录的主键，尤其是在需要确保跨多个数据库或系统的唯一性时。

9.15. XML Functions

PostgreSQL 中用于处理 XML 数据类型的函数。这些函数支持生成、查询、处理和映射 XML 数据，使得 PostgreSQL 能够作为一个强大的工具来处理 XML 格式的数据。以下是一些主要的 XML 函数及其用法：

  1. 生成 XML 内容：
    • xmlcomment(text) → xml：创建一个包含指定文本内容的 XML 注释。例如，xmlcomment(This is a comment) 生成 <!--This is a comment-->。
    • xmlconcat(xml, ...) → xml：连接多个 XML 值。例如，xmlconcat('<a/>, '<b/>) 生成 <a/><b/>。
    • xmlelement(name text, XMLATTRIBUTES(...), content) → xml：创建一个具有指定名称、属性和内容的 XML 元素。例如，xmlelement(name "item", xmlattributes(value as "type"), content) 生成 <item type="value">content</item>。
    • xmlforest(... AS name) → xml：创建一个 XML 森林，其中每个参数成为一个独立的元素。例如，xmlforest(content AS "item") 生成 <item>content</item>。
  2. 处理和查询 XML：
    • xpath(text, xml, array[text]) → xml[]：对 XML 值执行 XPath 查询。例如，xpath('/a/b/text()', '<a><b>test</b></a>') 返回 {test}。
    • xpath_exists(text, xml, array[text]) → boolean：检查 XPath 查询是否在给定的 XML 中返回至少一个节点。例如，xpath_exists('/a/b, '<a><b>test</b></a>') 返回 true。
    • xml_is_well_formed(text) → boolean：检查给定的文本是否是格式良好的 XML。例如，xml_is_well_formed('<a><b></b></a>') 返回 true。
    • xml_is_well_formed_document(text) → boolean：检查给定的文本是否是格式良好的 XML 文档，即是否有单一的根元素。
  3. 映射表到 XML：
    • table_to_xml(table regclass, nulls boolean, tableforest boolean, targetns text) → xml：将表的内容映射为 XML。例如，table_to_xml(my_table::regclass, true, false, '') 将表 my_table 的内容转换为 XML 格式。
    • query_to_xml(text, nulls boolean, tableforest boolean, targetns text) → xml：将 SQL 查询的结果映射为 XML。例如，query_to_xml(SELECT * FROM my_table, true, false, '') 将查询结果转换为 XML。
      这些 XML 函数提供了在 PostgreSQL 中处理 XML 数据的强大能力，从基本的生成和连接操作到复杂的查询和映射功能。

9.16、JSON Functions and Operators

PostgreSQL 中用于处理 JSON 和 JSONB 数据类型的函数和运算符。JSON 数据类型用于存储 JSON（JavaScript Object Notation）数据，而 JSONB 是其二进制表示形式，提供了更快的数据处理速度。以下是一些主要的 JSON 和 JSONB 函数和运算符及其用法：

  1. JSON/JSONB 运算符：
    • ->：获取 JSON 对象字段的 JSON 值或 JSON 数组元素的 JSON 值（使用整数索引）。例如，{"a": {"b":"foo"}}::json->a 返回 {"b":"foo"}。
    • ->>：获取 JSON 对象字段的文本值或 JSON 数组元素的文本值。例如，{"a":1,"b":2}::json->>b 返回 2。
    • #>：使用字符串数组指定的路径获取 JSON 对象中嵌套的 JSON 值。例如，{"a": {"b": ["foo","bar"]}}::json#>{a,b,1} 返回 "bar"。
    • #>>：使用字符串数组指定的路径获取 JSON 对象中嵌套的文本值。
  2. JSON/JSONB 函数：
    • json_build_object(VARIADIC "any") → json / jsonb_build_object(VARIADIC "any") → jsonb：使用提供的键值对构建一个 JSON 对象。例如，json_build_object(foo, 1, bar, 2) 返回 {"foo": 1, "bar": 2}。
    • json_build_array(VARIADIC "any") → json / jsonb_build_array(VARIADIC "any") → jsonb：使用提供的值构建一个 JSON 数组。例如，json_build_array(1, 2, 3) 返回 [1, 2, 3]。
    • json_object(text[]) → json / jsonb_object(text[]) → jsonb：从键值对数组创建一个 JSON 对象。例如，json_object(array[foo, 1, bar, 2]) 返回 {"foo": "1", "bar": "2"}。
    • json_array_length(json) → integer / jsonb_array_length(jsonb) → integer：返回 JSON 数组的长度。例如，json_array_length('[1,2,3,4]) 返回 4。
    • json_typeof(json) → text / jsonb_typeof(jsonb) → text：返回顶层 JSON 值的类型（如 object, array, string, number, boolean, 或 null）。例如，json_typeof({"a":1}') 返回 "object"。
    • to_json(anyelement) → json / to_jsonb(anyelement) → jsonb：将任意 SQL 值转换为 JSON/JSONB 值。例如，to_jsonb(Hello, world!::text) 返回 "Hello, world!"。
      这些运算符和函数为在 PostgreSQL 中处理 JSON 数据提供了强大的支持，使得用户可以轻松地查询、修改和生成 JSON 数据。

9.17. Sequence Manipulation Functions

PostgreSQL 中用于操作序列对象的函数。序列是一种特殊的单行表，用于生成唯一的数值标识符，通常用于自增主键。以下是一些主要的序列操作函数及其用法：

  1. nextval(regclass) → bigint：
    • 递增序列对象到其下一个值，并返回该值。这是原子操作，即使在多个会话中并发执行，每个会话也会安全地接收到一个独特的序列值。例如，SELECT nextval(my_seq); 可能返回序列的下一个值。
  2. currval(regclass) → bigint：
    • 返回当前会话中最近通过 nextval 获取的指定序列的值。如果在当前会话中未曾调用过 nextval，则会报错。例如，SELECT currval(my_seq); 返回当前会话中最后一次调用 nextval(my_seq) 所返回的值。
  3. setval(regclass, bigint, boolean) → bigint：
    • 设置序列的当前值，并根据第三个参数决定下一次调用 nextval 时返回的值。如果第三个参数为 true（默认值），nextval 将返回设置值加一；如果为 false，nextval 将返回设置的值。例如，SELECT setval(my_seq, 42, false); 将使下一次 nextval(my_seq) 调用返回 42。
  4. setval(regclass, bigint) → bigint：
    • 这是 setval 函数的简化版本，第三个参数默认为 true。这意味着序列将被设置为指定的值，下一次调用 nextval 时将返回该值加一。
      这些函数允许开发者直接控制序列的值，非常适合于需要手动调整序列或在数据迁移和同步时保持数据一致性的情况。使用序列函数可以确保在数据库中生成唯一的数值标识符，对于管理自增主键非常有用。

9.18. Conditional Expressions

PostgreSQL 中用于执行条件逻辑的表达式。这些表达式允许在查询中根据条件动态地返回不同的结果，类似于其他编程语言中的 if-else 逻辑。以下是一些主要的条件表达式及其用法：

  1. CASE：
    • CASE 表达式是最通用的条件表达式，它按顺序评估一系列条件，直到找到一个为 true 的条件，然后返回该条件对应的结果。如果没有任何条件为 true，它将返回 ELSE 子句中的结果（如果有的话），否则返回 NULL。例如：
      
SELECT 
  CASE 
    WHEN score >= 90 THEN Excellent
    WHEN score >= 75 THEN Good
    WHEN score >= 60 THEN Fair
    ELSE Poor
  END

FROM grades;
    • 这个查询会根据 score 字段的值返回不同的评级。
  2. COALESCE：
    • COALESCE 函数返回其参数列表中的第一个非 NULL 值。如果所有参数都是 NULL，则返回 NULL。这个函数非常有用，当你需要从一系列可能的 NULL 值中选择第一个实际的值时。例如：
      
SELECT COALESCE(address1, address2, Unknown) FROM customers;
    • 这个查询尝试返回 address1，如果 address1 是 NULL，则尝试 address2，如果两者都是 NULL，则返回 Unknown。
  3. NULLIF：
    • NULLIF 函数返回第一个参数的值，但如果第一个参数和第二个参数相等，则返回 NULL。这在需要避免除以零时产生错误的计算中特别有用。例如：
      
SELECT NULLIF(value, 0) FROM compute_values;
    • 这个查询在 value 不等于 0 时返回 value，否则返回 NULL。
  4. GREATEST 和 LEAST：
    • GREATEST 和 LEAST 函数分别返回其所有参数中的最大值和最小值。如果任何参数为 NULL，则这些函数将返回 NULL。例如：
      
SELECT GREATEST(1, 2, 3), LEAST(1, 2, 3);
    • 这个查询返回 3 和 1。
      这些条件表达式提供了在 SQL 查询中实现复杂逻辑的能力，使得数据检索可以根据动态条件灵活地调整返回的结果。

9.19. Array Functions and Operators

PostgreSQL 中用于操作数组类型的函数和运算符。数组是 PostgreSQL 中的一种数据类型，允许在单个变量中存储多个值。以下是一些主要的数组函数和运算符及其用法：

  1. 数组运算符：
    • @>：检查左侧数组是否包含右侧数组。例如，ARRAY[1,2,3] @> ARRAY[2,3] 返回 true。
    • <@：检查左侧数组是否被右侧数组包含。例如，ARRAY[2,3] <@ ARRAY[1,2,3,4] 返回 true。
    • &&：检查两个数组是否有公共元素。例如，ARRAY[1,2,3] && ARRAY[3,4,5] 返回 true。
    • ||：数组连接运算符，将两个数组合并成一个数组。例如，ARRAY[1,2,3] || ARRAY[4,5] 返回 ARRAY[1,2,3,4,5]。
  2. 数组函数：
    • array_append(anyarray, anyelement)：向数组末尾添加一个元素。例如，array_append(ARRAY[1,2,3], 4) 返回 ARRAY[1,2,3,4]。
    • array_prepend(anyelement, anyarray)：向数组开头添加一个元素。例如，array_prepend(0, ARRAY[1,2,3]) 返回 ARRAY[0,1,2,3]。
    • array_cat(anyarray, anyarray)：连接两个数组。例如，array_cat(ARRAY[1,2], ARRAY[3,4]) 返回 ARRAY[1,2,3,4]。
    • array_length(anyarray, int)：返回数组在指定维度的长度。例如，array_length(ARRAY[[1,2],[3,4]], 1) 返回 2。
    • array_dims(anyarray)：返回数组的维度和每个维度的长度。例如，array_dims(ARRAY[[1,2,3],[4,5,6]]) 返回 [1:2][1:3]。
    • unnest(anyarray)：将数组展开为一组行。例如，对于 unnest(ARRAY[1,2,3])，每个数组元素成为结果集中的一行。
      这些运算符和函数为在 PostgreSQL 中处理数组数据提供了强大的支持，使得用户可以轻松地查询、修改、合并和扩展数组数据。

9.20. Range/Multirange Functions and Operators

PostgreSQL 中用于操作范围（range）类型和多范围（multirange）类型的函数和运算符。范围类型是 PostgreSQL 的一个特性，允许用户在单个数据类型中表示值的连续区间，非常适用于需要处理时间段、数值区间等场景。多范围类型则允许表示多个这样的连续区间。以下是一些主要的范围和多范围函数及运算符及其用法：

  1. 范围运算符：
    • @>：检查左侧范围是否包含右侧范围或元素。例如，int4range(1, 5) @> 3 返回 true。
    • <@：检查左侧范围或元素是否被右侧范围包含。例如，3 <@ int4range(1, 5) 返回 true。
    • &&：检查两个范围是否重叠。例如，int4range(1, 5) && int4range(4, 10) 返回 true。
    • <<：检查左侧范围是否严格在右侧范围之前。例如，int4range(1, 3) << int4range(5, 10) 返回 true。
    • >>：检查左侧范围是否严格在右侧范围之后。例如，int4range(5, 10) >> int4range(1, 3) 返回 true。
  2. 多范围运算符：
    • 多范围类型支持类似的运算符，例如 @>、<@、&&，但适用于表示多个区间的情况。
  3. 范围函数：
    • lower(anyrange)：返回范围的下界。
    • upper(anyrange)：返回范围的上界。
    • isempty(anyrange)：检查范围是否为空。
    • lower_inc(anyrange)：检查范围的下界是否包含在内。
    • upper_inc(anyrange)：检查范围的上界是否包含在内。
  4. 多范围函数：
    • lower(anymultirange)：返回多范围中的最小下界。
    • upper(anymultirange)：返回多范围中的最大上界。
    • isempty(anymultirange)：检查多范围是否为空。
    • range_merge(anyrange)：合并重叠或相邻的范围为一个范围。
    • multirange(anyrange)：将范围转换为多范围类型。
      这些功能提供了强大的工具来处理包含连续区间的数据，非常适合于时间段管理、数值区间查询等应用场景。

9.21. Aggregate Functions

PostgreSQL 中用于执行聚合计算的函数。聚合函数对一组值执行计算，返回一个单一的值。这些函数通常用于 GROUP BY 子句中，用于统计分析、数据汇总等场景。以下是一些主要的聚合函数及其用法：

  1. 常用聚合函数：
    • AVG(expression)：计算表达式的平均值。仅适用于数值类型的列。
    • COUNT(expression) 或 COUNT(*)：计算非 NULL 值的数量。COUNT(*) 计算行数，不管列值是否为 NULL。
    • MAX(expression)：返回表达式的最大值。
    • MIN(expression)：返回表达式的最小值。
    • SUM(expression)：计算表达式的总和。仅适用于数值类型的列。
  2. 特殊聚合函数：
    • ARRAY_AGG(expression)：将表达式的值聚合成一个数组。
    • STRING_AGG(expression, delimiter)：使用指定的分隔符将字符串值聚合成一个字符串。
    • BOOL_AND(expression)：如果所有输入值均为 TRUE，则返回 TRUE；否则返回 FALSE。
    • BOOL_OR(expression)：如果任一输入值为 TRUE，则返回 TRUE；否则返回 FALSE。
    • JSON_AGG(expression) 和 JSONB_AGG(expression)：将表达式的值聚合成一个 JSON 或 JSONB 数组。
  3. 高级聚合函数：
    • PERCENTILE_CONT(fraction)：计算连续百分位数。fraction 是一个介于 0 和 1 之间的值，表示百分位。
    • PERCENTILE_DISC(fraction)：计算离散百分位数。
    • MODE()：计算众数，即出现次数最多的值。
      聚合函数是 SQL 查询中处理数据汇总和分析不可或缺的工具。通过使用聚合函数，可以轻松实现对数据集的统计分析，比如计算总和、平均值、最大值、最小值等。

9.22. Window Functions

PostgreSQL 中的窗口函数，这些函数提供了一种在查询结果集的“窗口”上执行计算的能力。与聚合函数不同，窗口函数不会将行组合成单个输出行，它们会为每个输入行返回一个值。这使得窗口函数特别适用于需要保留原始行数据的计算，如累计总和、移动平均、排名等。
以下是一些主要的窗口函数及其用法：

  1. 排名函数：
    • ROW_NUMBER()：为结果集的每一行分配一个唯一的连续整数。
    • RANK()：为结果集中的每一行分配一个排名，相同值的行会获得相同的排名，并在下一个排名时留下间隔。
    • DENSE_RANK()：与 RANK() 类似，但是排名之间不会留下间隔。
    • PERCENT_RANK()：计算行的相对排名，作为从 0 到 1 的分数。
  2. 分析函数：
    • CUME_DIST()：计算当前行的累积分布，即小于等于当前行值的行数占总行数的比例。
    • NTILE(num_buckets)：将结果集分成指定数量的等分，为每一行分配一个桶号。
  3. 值函数：
    • LAG(value [, offset [, default]])：返回当前行之前的某行的值。
    • LEAD(value [, offset [, default]])：返回当前行之后的某行的值。
    • FIRST_VALUE(value)：返回窗口内的第一个值。
    • LAST_VALUE(value)：返回窗口内的最后一个值。
    • NTH_VALUE(value, n)：返回窗口内的第 n 个值。
      窗口函数通过在其 OVER 子句中指定的窗口定义来执行。窗口定义可以包括分区（PARTITION BY）、排序（ORDER BY）以及窗口范围（ROWS/RANGE BETWEEN）。
      窗口函数为数据分析提供了强大的工具，允许在保持行级别详细信息的同时执行复杂的计算。

9.23. Subquery Expressions

PostgreSQL 中支持的与子查询相关的表达式。子查询是嵌入在另一个查询中的 SQL 查询，可以用于在 SELECT、INSERT、UPDATE、DELETE 语句中。子查询表达式允许在查询中使用更复杂的条件逻辑，使得数据查询更加灵活和强大。以下是一些主要的子查询表达式及其用法：

  1. EXISTS：
    • EXISTS (subquery)：如果子查询返回至少一行，则结果为 true。常用于检查是否存在满足特定条件的记录。例如：
      
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM department WHERE employees.department_id = department.id);
  2. IN：
    • expression IN (subquery)：如果表达式的值等于子查询返回的任何值，则结果为 true。例如：
      
SELECT * FROM employees WHERE department_id IN (SELECT id FROM department WHERE name = Sales);
  3. ANY / SOME：
    • expression operator ANY (subquery) 或 expression operator SOME (subquery)：如果表达式与子查询返回的任何值满足给定的比较运算符，则结果为 true。例如：
      
SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE price < 100);
  4. ALL：
    • expression operator ALL (subquery)：如果表达式与子查询返回的所有值都满足给定的比较运算符，则结果为 true。例如：
      
SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE price < 100);
  5. ROW Subqueries：
    • 子查询也可以返回一行多列数据，用于和外层查询中的行构造器进行比较。例如：
      
SELECT * FROM employees WHERE (department_id, position_id) IN (SELECT department_id, position_id FROM department_positions);

子查询表达式扩展了 SQL 查询的能力，允许进行更为复杂的数据检索和分析。通过合理使用子查询，可以有效地解决一些复杂的查询需求。

9.24. Row and Array Comparisons

PostgreSQL 中用于比较行和数组的特殊构造和运算符。这些功能允许开发者在查询中执行复杂的比较逻辑，比如比较整行或数组的内容。以下是一些主要的行和数组比较功能及其用法：

  1. 行比较：
    • PostgreSQL 允许直接比较两个行构造器（使用小括号和逗号分隔的值列表），支持的比较运算符包括 =, <>, <, <=, >, >=。例如：
      
SELECT * FROM table1 WHERE (col1, col2) = (val1, val2);
    • 行比较可以用于 WHERE 和 JOIN 条件中，提供一种简洁的方式来比较多个列。
  2. 数组比较：
    • PostgreSQL 提供了一系列数组比较运算符，如 @>, <@, &&，分别用于测试数组是否包含另一个数组、是否被另一个数组包含、以及两个数组是否有共同元素。例如：
      
SELECT * FROM table1 WHERE array_col @> ARRAY[val1, val2];
  3. ANY 和 ALL 运算符：
    • 当需要将单个值与数组中的元素进行比较时，可以使用 ANY 和 ALL 运算符。这些运算符可以与标准比较运算符结合使用，例如：
      
SELECT * FROM table1 WHERE col1 = ANY(array_col);
    • ANY 用于测试是否至少有一个数组元素满足条件，而 ALL 用于测试所有数组元素是否都满足条件。
  4. 特殊的行比较运算符：
    • 对于复合类型（行类型）的比较，PostgreSQL 定义了一组特殊的运算符 *=、*<>、*<、*<=、*>、*>=，这些运算符比较的是行的内部二进制表示。这主要用于系统内部操作，如物化视图的刷新，一般不推荐在常规查询中使用。
      这些行和数组的比较功能极大地增强了 PostgreSQL 在处理复杂数据结构时的灵活性和表达能力。

9.25. Set Returning Functions

PostgreSQL 中的集合返回函数，这些函数能够返回多于一行的结果集。这类函数特别适用于生成系列数据、展开数组或者执行其他返回多行结果的操作。以下是一些主要的集合返回函数及其用法：

  1. Series Generating Functions：
    • generate_series(start, stop[, step])：生成一个序列，从 start 到 stop，可指定步长 step。这个函数有多个变体，可以生成整数、大整数（bigint）、数值（numeric）或时间戳（timestamp）系列。例如：
      
SELECT * FROM generate_series(1, 5);
      生成从 1 到 5 的整数系列。
  2. Array Expansion：
    • unnest(array)：将一个数组展开成一系列行。如果提供多个数组参数，unnest 会并行展开它们，每个数组的相同位置元素组成一行。例如：
      
SELECT * FROM unnest(ARRAY[1,2], ARRAY[a,b]);
      展开成两行，每行包含两个元素。
  3. Set Returning Functions with Ordinality：
    • 当使用集合返回函数时，可以通过 WITH ORDINALITY 向结果集添加一个表示行序号的列。例如：
      
SELECT * FROM unnest(ARRAY[a, b, c]) WITH ORDINALITY;
      返回的结果集中将包含元素和它们的序号。
      集合返回函数的使用在数据分析、报表生成以及需要生成或展开数据集时非常有用。它们提供了一种灵活的方式来生成或转换数据，以便进一步查询或处理。

9.26. System Information Functions and Operators

PostgreSQL 中用于获取各种系统和安装信息的函数和运算符。这些函数可以帮助开发者和数据库管理员获取关于 PostgreSQL 实例的详细信息，包括会话信息、版本信息、配置设置等。以下是一些主要的系统信息函数和运算符及其用法：

  1. 会话信息函数：
    • pg_backend_pid()：返回当前 PostgreSQL 会话的进程 ID。
    • pg_conf_load_time()：返回最后一次配置文件被加载的时间。
    • pg_is_in_recovery()：返回一个布尔值，指示 PostgreSQL 是否处于恢复模式中。
  2. 版本和可用功能：
    • version()：返回 PostgreSQL 的版本信息。
    • pg_postmaster_start_time()：返回 PostgreSQL 主进程（postmaster）启动的时间。
    • pg_available_extensions()：列出可用的 PostgreSQL 扩展。
  3. 配置设置函数：
    • current_setting(setting_name)：获取指定配置参数的当前设置值。
    • set_config(setting_name, new_value, is_local)：动态设置会话级别的配置参数。
  4. 统计信息和日志：
    • pg_stat_reset()：重置所有统计计数器。
    • pg_reload_conf()：重新加载服务器配置文件，无需重启数据库。
  5. 系统目录和权限：
    • pg_has_role(role_name, role_privilege)：检查当前用户是否具有指定角色的特定权限。
    • pg_database_size(dbname)：返回指定数据库的大小。
    • pg_tablespace_size(tablespace_name)：返回指定表空间的大小。
  6. 其他有用的系统函数：
    • pg_cancel_backend(pid)：尝试取消指定进程 ID 的后台进程。
    • pg_terminate_backend(pid)：尝试终止指定进程 ID 的后台进程。
    • pg_sleep(seconds)：使当前会话暂停指定的秒数。
      这些系统信息函数和运算符对于监控 PostgreSQL 的状态、性能调优以及日常数据库维护非常有用。它们使得从数据库内部获取关键信息变得直接而简单。

9.27. System Administration Functions

PostgreSQL 中用于数据库系统管理的一系列函数。这些函数允许数据库管理员执行各种管理任务，如配置管理、服务器信号处理、文件访问、备份控制等。以下是一些主要的系统管理函数及其用法：

  1. 配置设置函数：
    • current_setting(setting_name)：获取指定配置参数的当前设置值。
    • set_config(setting_name, new_value, is_local)：动态地在会话级别或事务级别设置配置参数的值。
  2. 服务器信号函数：
    • pg_reload_conf()：使服务器重新加载配置文件，无需重启。
    • pg_rotate_logfile()：请求日志文件的即时轮换。
  3. 文件访问函数：
    • pg_read_file(filename [, offset [, length]])：读取服务器上的文件内容。出于安全考虑，这通常限制于只能读取数据库的日志文件和其他特定目录下的文件。
    • pg_ls_dir(dirname)：列出服务器上指定目录下的文件和目录名。
  4. 备份控制函数：
    • pg_start_backup(label [, exclusive [, fast]])：开始一个基础备份过程。
    • pg_stop_backup()：结束一个基础备份过程。
  5. 复制控制函数：
    • pg_create_physical_replication_slot(slot_name)：创建一个物理复制槽。
    • pg_drop_replication_slot(slot_name)：删除指定的复制槽。
  6. 索引维护函数：
    • pg_reindexdb(options)：重新索引数据库中的所有表或指定的表。
  7. 数据库对象管理：
    • pg_relation_size(relname)：返回指定表或索引的大小。
    • pg_database_size(dbname)：返回指定数据库的大小。
  8. 锁管理和监控：
    • pg_locks 视图提供了当前锁的信息，而 pg_try_advisory_lock(key) 函数尝试获取一个顾问锁。
  9. 会话和进程管理：
    • pg_terminate_backend(pid)：终止指定的后台进程。
    • pg_cancel_backend(pid)：取消指定进程的当前查询。
      这些系统管理函数为 PostgreSQL 的维护和管理提供了强大的工具，使得数据库管理员能够有效地控制和优化数据库环境。

9.28. Trigger Functions

PostgreSQL 中与触发器相关的函数。触发器是数据库管理系统中的一种机制，它们是自动执行的数据库对象，用于在特定数据库事件发生时自动执行一段指定的 SQL 代码或函数。这些事件包括表的 INSERT、UPDATE 或 DELETE 操作。以下是一些主要的触发器函数及其用法：

  1. 内置触发器函数：
    • suppress_redundant_updates_trigger()：这是一个内置的触发器函数，用于阻止不必要的更新操作。如果更新操作没有实际改变任何数据，则此触发器会阻止该更新操作，从而提高效率。
    • tsvector_update_trigger()：这个触发器函数自动更新 tsvector 列（用于全文搜索），根据与之相关联的普通文本列的内容。
  2. 创建触发器：
    • 使用 CREATE TRIGGER 语句创建触发器，并指定触发器的名称、触发时机（BEFORE、AFTER、INSTEAD OF）、触发事件（INSERT、UPDATE、DELETE、TRUNCATE）和触发器要调用的函数。例如：
      
CREATE TRIGGER example_trigger BEFORE UPDATE ON example_table

FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();
  3. 事件触发器函数：
    • PostgreSQL 还支持事件触发器，这是一种特殊类型的触发器，用于响应非 DML 事件，如 DDL 操作（创建、更改或删除数据库对象）。事件触发器使用 CREATE EVENT TRIGGER 语句创建，并可以执行如记录操作、审计或其他自定义逻辑的函数。
  4. 触发器使用注意事项：
    • 触发器可以非常强大，但如果使用不当，也可能导致性能问题。例如，过多的触发器调用或复杂的触发器逻辑可能会降低数据库操作的速度。
    • 触发器函数通常使用 PL/pgSQL（PostgreSQL 的过程语言）编写，但也可以使用其他支持的过程语言。
      触发器在数据库设计中扮演着重要角色，它们可以用于实现复杂的业务逻辑、数据完整性检查、自动化任务等。

9.29. Event Trigger Functions

PostgreSQL 中用于处理事件触发器的函数。事件触发器是一种特殊类型的触发器，它们在指定的数据库事件发生时自动执行，如数据库结构定义语言（DDL）操作（例如，创建、更改或删除数据库对象）。这些函数提供了获取事件触发器执行上下文信息的能力，使得开发者可以在事件触发器中实现更复杂的逻辑。
以下是一些主要的事件触发器函数及其用法：

  1. pg_event_trigger_ddl_commands()：
    • 返回执行 DDL 命令的列表。这个函数在与 ddl_command_end 事件触发器一起使用时特别有用，它允许你检查哪些 DDL 命令被执行，并根据这些信息执行相应的逻辑。
    • 示例用法：
      
CREATE FUNCTION log_ddl_commands() RETURNS event_trigger AS $$
BEGIN

  RAISE NOTICE DDL command: %, pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER log_ddl ON ddl_command_end

EXECUTE FUNCTION log_ddl_commands();
  2. pg_event_trigger_dropped_objects()：
    • 返回在执行 DROP 命令时被删除的对象的列表。这个函数可以用于记录或审计被删除的数据库对象。
    • 示例用法：
      
CREATE FUNCTION log_dropped_objects() RETURNS event_trigger AS $$
BEGIN

  RAISE NOTICE Dropped objects: %, pg_event_trigger_dropped_objects();
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER log_drops ON sql_drop

EXECUTE FUNCTION log_dropped_objects();
  3. pg_event_trigger_table_rewrite_oid() 和 pg_event_trigger_table_rewrite_reason()：
    • 这些函数提供了关于表重写事件的信息，例如，当执行 ALTER TABLE 操作需要重写整个表时。这可以用于监控那些可能对数据库性能产生影响的操作。
    • 示例用法：
      
CREATE FUNCTION log_table_rewrite() RETURNS event_trigger AS $$
BEGIN

  RAISE NOTICE Table rewrite OID: %, Reason: %, pg_event_trigger_table_rewrite_oid(), pg_event_trigger_table_rewrite_reason();
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER log_rewrite ON table_rewrite

EXECUTE FUNCTION log_table_rewrite();

事件触发器函数为 PostgreSQL 提供了强大的扩展能力，允许开发者在数据库级别实现复杂的事件响应逻辑，如自动化任务、审计跟踪或自定义的安全检查。

9.30. Statistics Information Functions

PostgreSQL 中用于访问和管理统计信息的函数。这些统计信息对于数据库优化和查询计划的生成非常重要，因为它们提供了关于数据分布、表大小以及索引使用情况等的详细信息。以下是一些主要的统计信息函数及其用法：

  1. pg_stat_reset()：
    • 重置所有统计计数器。这对于在进行性能测试或者在特定事件后想要从零开始收集统计信息时非常有用。
  2. pg_stat_get_backend_idset()：
    • 返回当前活动的后台进程的标识符集合。这可以用于监控和分析数据库的当前活动。
  3. pg_stat_get_db_numbackends(db_oid)：
    • 返回指定数据库中活动后台进程的数量。这有助于了解数据库的负载情况。
  4. pg_stat_get_tables() 和 pg_stat_get_indexes()：
    • 分别返回数据库中所有表和索引的统计信息，包括扫描次数、元组读取和修改次数等。这些信息对于分析表和索引的使用情况以及优化查询性能非常有用。
  5. pg_stat_user_tables 和 pg_stat_user_indexes 视图：
    • 提供了用户表和索引的统计信息的高级视图。这些视图使得查询统计信息更加方便和直观。
  6. pg_stat_activity 视图：
    • 显示当前所有活动会话的信息，包括查询、状态、客户端地址等。这对于监控数据库活动和性能调优非常重要。
  7. pg_stats 视图：
    • 提供列级别的统计信息，如数据分布的直方图和最常见的值。这对于理解数据的分布特性以及优化查询计划非常有价值。
  8. pg_size_pretty(size bigint)：
    • 将字节大小转换为更易读的格式（如 MB、GB）。这对于报告和监控数据库大小非常有用。
      这些统计信息函数和视图为数据库管理员和开发者提供了强大的工具，以监控、分析和优化 PostgreSQL 数据库的性能。
墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论