PostgreSQL 集合返回函数详解

概述

集合返回函数（Set-Returning Functions，SRF）是 PostgreSQL 中一类特殊的函数，它们能够返回多行数据而不是单个值。这类函数在数据生成、数组处理、时间序列分析等场景中发挥着重要作用。

什么是集合返回函数？

集合返回函数是指那些可能返回多行（零行、一行或多行）的函数。与普通函数返回单个值不同，SRF 可以作为表达式使用，在 FROM 子句中充当数据源。

序列生成函数 generate_series

数值序列生成

generate_series 是最常用的集合返回函数，可以生成指定范围内的数值序列。

函数签名

函数签名	描述
`generate_series(start integer, stop integer [, step integer])`	生成整数序列
`generate_series(start bigint, stop bigint [, step bigint])`	生成大整数序列
`generate_series(start numeric, stop numeric [, step numeric])`	生成数值序列

基础用法示例

递增序列递减序列小数序列

sql

-- 生成 2 到 4 的整数序列
SELECT * FROM generate_series(2, 4);

-- 输出结果：
-- generate_series
-- ----------------
--               2
--               3
--               4
-- (3 rows)

sql

-- 生成 5 到 1 的递减序列，步长为 -2
SELECT * FROM generate_series(5, 1, -2);

-- 输出结果：
-- generate_series
-- ----------------
--               5
--               3
--               1
-- (3 rows)

sql

-- 生成小数序列
SELECT generate_series(1.1, 4, 1.3);

-- 输出结果：
-- generate_series
-- ----------------
--             1.1
--             2.4
--             3.7
-- (3 rows)

实际业务场景应用

场景 1：生成测试数据

sql

-- 创建用户测试数据
SELECT
    'user_' || s.id AS username,
    'user' || s.id || '@example.com' AS email,
    CASE WHEN s.id % 2 = 0 THEN '男' ELSE '女' END AS gender
FROM generate_series(1, 5) AS s(id);

-- 输出结果：
-- username | email              | gender
-- ---------|-------------------|-------
-- user_1   | [email protected] | 女
-- user_2   | [email protected] | 男
-- user_3   | [email protected] | 女
-- user_4   | [email protected] | 男
-- user_5   | [email protected] | 女

场景 2：填补缺失的序列

sql

-- 假设有一个订单表，某些订单号缺失
CREATE TEMP TABLE orders (order_id int, amount decimal);
INSERT INTO orders VALUES (1, 100), (3, 200), (5, 150);

-- 找出缺失的订单号
SELECT s.id AS missing_order_id
FROM generate_series(1, 5) AS s(id)
LEFT JOIN orders o ON s.id = o.order_id
WHERE o.order_id IS NULL;

-- 输出结果：
-- missing_order_id
-- ---------------
--               2
--               4

当 step 为正数时，如果 start > stop，返回零行；当 step 为负数时，如果 start < stop，返回零行。这个规则在编写动态查询时很重要。

时间序列生成

generate_series 也支持时间类型，这在时间序列分析中非常有用。

函数签名

函数签名	描述
`generate_series(start timestamp, stop timestamp, step interval)`	生成时间戳序列
`generate_series(start timestamptz, stop timestamptz, step interval [, timezone text])`	生成带时区的时间戳序列

时间序列示例

日期序列小时序列

sql

-- 生成最近 7 天的日期
SELECT current_date + s.a AS dates
FROM generate_series(0, 6, 1) AS s(a);

-- 输出结果（假设当前日期为 2024-02-05）：
-- dates
-- -----------
-- 2024-02-05
-- 2024-02-06
-- 2024-02-07
-- 2024-02-08
-- 2024-02-09
-- 2024-02-10
-- 2024-02-11

sql

-- 生成指定时间范围内每10小时的时间点
SELECT * FROM generate_series(
    '2008-03-01 00:00'::timestamp,
    '2008-03-04 12:00',
    '10 hours'
);

-- 输出结果：
-- generate_series
-- ---------------------
-- 2008-03-01 00:00:00
-- 2008-03-01 10:00:00
-- 2008-03-01 20:00:00
-- 2008-03-02 06:00:00
-- 2008-03-02 16:00:00
-- 2008-03-03 02:00:00
-- 2008-03-03 12:00:00
-- 2008-03-03 22:00:00
-- 2008-03-04 08:00:00

业务场景：销售报表时间维度

sql

-- 生成月度销售报表的时间维度
WITH monthly_dates AS (
    SELECT date_trunc('month', d)::date AS report_month
    FROM generate_series(
        '2024-01-01'::date,
        '2024-12-01'::date,
        '1 month'::interval
    ) AS s(d)
)
SELECT
    report_month,
    to_char(report_month, 'YYYY年MM月') AS month_name,
    CASE
        WHEN report_month <= current_date THEN '已过期'
        ELSE '未来月份'
    END AS status
FROM monthly_dates
ORDER BY report_month;

-- 分析过程：
-- 1. 使用 date_trunc 将日期截断到月份开始
-- 2. 生成全年的月份序列
-- 3. 添加中文月份显示和状态标识
-- 4. 按时间顺序排列

在处理带时区的时间序列时，要特别注意夏令时转换。PostgreSQL 会根据指定时区自动处理这些转换。

下标生成函数 generate_subscripts

generate_subscripts 专门用于处理数组，生成数组指定维度的有效下标序列。

函数签名

函数签名	描述
`generate_subscripts(array anyarray, dim integer)`	生成数组指定维度的下标
`generate_subscripts(array anyarray, dim integer, reverse boolean)`	可选择是否反向生成下标

基础数组下标示例

sql

-- 生成一维数组的下标
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;

-- 输出结果：
-- s
-- ---
-- 1
-- 2
-- 3
-- 4
-- (4 rows)

-- 分析过程：
-- 1. PostgreSQL 数组下标从 1 开始
-- 2. 即使数组元素为 NULL，下标仍然存在
-- 3. 函数返回所有有效的下标位置

数组遍历和处理

展示数组内容

sql

-- 创建测试表
CREATE TEMP TABLE arrays AS
SELECT '{-1,-2}'::int[] AS a
UNION ALL
SELECT '{100,200,300}'::int[];

-- 展示数组、下标和对应值
SELECT
    a AS array,
    s AS subscript,
    a[s] AS value
FROM (
    SELECT generate_subscripts(a, 1) AS s, a
    FROM arrays
) foo;

-- 输出结果：
-- array         | subscript | value
-- --------------|-----------|-------
-- {-1,-2}       |         1 |    -1
-- {-1,-2}       |         2 |    -2
-- {100,200,300} |         1 |   100
-- {100,200,300} |         2 |   200
-- {100,200,300} |         3 |   300

-- 分析过程：
-- 1. 子查询生成每个数组的下标
-- 2. 外层查询使用下标访问数组元素
-- 3. 实现了数组的完全展开

自定义二维数组展开函数

sql

-- 创建二维数组展开函数
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS $$
SELECT $1[i][j]
FROM generate_subscripts($1,1) g1(i),
     generate_subscripts($1,2) g2(j);
$$ LANGUAGE sql IMMUTABLE;

-- 使用示例
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);

-- 输出结果：
-- unnest2
-- ---------
--       1
--       2
--       3
--       4

-- 分析过程：
-- 1. 函数接受任意类型的数组作为参数
-- 2. 通过两层 generate_subscripts 生成二维坐标
-- 3. 使用 $1[i][j] 语法访问二维数组元素
-- 4. 将二维数组完全展开为一维结果集

实际业务场景：标签数组处理

sql

-- 商品标签处理场景
CREATE TEMP TABLE products (
    id int,
    name text,
    tags text[]
);

INSERT INTO products VALUES
(1, '智能手机', ARRAY['电子产品', '通讯设备', '智能硬件']),
(2, '笔记本电脑', ARRAY['电子产品', '办公设备']),
(3, '运动鞋', ARRAY['服装配饰', '运动用品']);

-- 展开所有商品的标签
SELECT
    p.id,
    p.name,
    s.tag_index,
    p.tags[s.tag_index] AS tag_name
FROM products p
CROSS JOIN LATERAL (
    SELECT generate_subscripts(p.tags, 1) AS tag_index
) s
ORDER BY p.id, s.tag_index;

-- 输出结果：
-- id | name       | tag_index | tag_name
-- ---|------------|-----------|----------
--  1 | 智能手机    |         1 | 电子产品
--  1 | 智能手机    |         2 | 通讯设备
--  1 | 智能手机    |         3 | 智能硬件
--  2 | 笔记本电脑  |         1 | 电子产品
--  2 | 笔记本电脑  |         2 | 办公设备
--  3 | 运动鞋     |         1 | 服装配饰
--  3 | 运动鞋     |         2 | 运动用品

为什么使用 LATERAL 连接？

LATERAL 关键字允许右侧的子查询引用左侧表的列。在这个例子中：

左侧：products p 表
右侧：子查询引用了 p.tags 来生成下标

没有 LATERAL，子查询无法访问外层查询的列。

WITH ORDINALITY 子句

WITH ORDINALITY 是一个强大的功能，为集合返回函数的输出添加行号列。

基本语法和使用

sql

-- 为文件列表添加序号
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls, n);

-- 输出结果：
-- ls              | n
-- ----------------|----
-- pg_serial       |  1
-- pg_twophase     |  2
-- postmaster.opts |  3
-- pg_notify       |  4
-- postgresql.conf |  5
-- pg_tblspc       |  6
-- logfile         |  7
-- base            |  8
-- ...

-- 分析过程：
-- 1. pg_ls_dir() 返回目录中的文件列表
-- 2. WITH ORDINALITY 添加从 1 开始的行号
-- 3. AS t(ls, n) 为列指定别名

实际应用场景

场景 1：数据排名编号

sql

-- 为销售数据添加排名
WITH sales_data AS (
    SELECT unnest(ARRAY['张三', '李四', '王五', '赵六']) AS salesperson,
           unnest(ARRAY[15000, 12000, 18000, 9000]) AS sales_amount
)
SELECT * FROM (
    SELECT salesperson, sales_amount
    FROM sales_data
    ORDER BY sales_amount DESC
) ranked_sales WITH ORDINALITY AS t(salesperson, sales_amount, rank);

-- 输出结果：
-- salesperson | sales_amount | rank
-- ------------|--------------|------
-- 王五        |        18000 |    1
-- 张三        |        15000 |    2
-- 李四        |        12000 |    3
-- 赵六        |         9000 |    4

场景 2：数组元素索引

sql

-- 为数组元素添加索引号
SELECT * FROM unnest(ARRAY['苹果', '香蕉', '橘子', '葡萄'])
WITH ORDINALITY AS fruit_list(fruit_name, position);

-- 输出结果：
-- fruit_name | position
-- -----------|---------
-- 苹果       |        1
-- 香蕉       |        2
-- 橘子       |        3
-- 葡萄       |        4

-- 分析过程：
-- 1. unnest() 将数组展开为行
-- 2. WITH ORDINALITY 为每行添加位置信息
-- 3. 可用于需要知道元素原始位置的场景

性能优化和最佳实践

性能考量

性能注意事项

大范围序列生成：generate_series(1, 10000000) 会生成一千万行，消耗大量内存
嵌套使用：多个 SRF 嵌套使用时，要注意笛卡尔积效应
索引使用：SRF 生成的数据无法使用索引，需要在后续处理中考虑

sql

-- 错误示例：可能导致性能问题
SELECT
    generate_series(1, 1000),
    generate_series(1, 1000)  -- 这会产生 1000 × 1000 = 100万行！
LIMIT 10;

-- 正确示例：使用 LATERAL 避免笛卡尔积
SELECT s1.val, s2.val
FROM generate_series(1, 10) s1(val)
CROSS JOIN LATERAL generate_series(s1.val, s1.val + 2) s2(val);

最佳实践总结

场景	建议做法	原因
大数据量生成	分批处理或使用 LIMIT	避免内存溢出
多个 SRF 组合	使用 LATERAL 连接	避免笛卡尔积
时间序列	指定明确的时区	避免夏令时问题
数组处理	结合 unnest() 使用	提高可读性
测试数据生成	使用 WITH 子句组织	提高查询可维护性

常见错误和解决方案

错误 1：步长为零

sql

-- 错误示例
SELECT * FROM generate_series(1, 10, 0);
-- ERROR: step size cannot equal zero

-- 解决方案：确保步长不为零
SELECT * FROM generate_series(1, 10, 1);

错误 2：数组维度超出范围

sql

-- 错误示例
SELECT generate_subscripts(ARRAY[1,2,3], 2);  -- 一维数组请求二维下标
-- 返回空结果集

-- 解决方案：确认数组维度
SELECT array_ndims(ARRAY[1,2,3]);  -- 返回 1
SELECT generate_subscripts(ARRAY[1,2,3], 1);  -- 正确

错误 3：NULL 输入处理

sql

-- NULL 输入会返回空结果
SELECT * FROM generate_series(NULL, 10);  -- 返回 0 行
SELECT * FROM generate_series(1, NULL);   -- 返回 0 行

-- 解决方案：使用 COALESCE 提供默认值
SELECT * FROM generate_series(COALESCE(NULL, 1), 10);

总结

集合返回函数是 PostgreSQL 中非常强大的工具，主要应用场景包括：

数据生成：测试数据、序列号、时间序列
数组处理：数组展开、元素遍历、索引操作
报表分析：时间维度构建、数据填充
系统管理：文件列表、配置遍历

掌握这些函数能够大大提高 PostgreSQL 的数据处理效率，特别是在需要动态生成数据或处理复杂数据结构的场景中。

在使用集合返回函数时，要始终考虑性能影响，特别是在处理大数据量时。合理使用 LIMIT、分批处理和适当的索引策略是关键。

PostgreSQL 集合返回函数详解 ​

概述 ​

序列生成函数 generate_series ​

数值序列生成 ​

函数签名 ​

基础用法示例 ​

实际业务场景应用 ​

时间序列生成 ​

函数签名 ​

时间序列示例 ​

业务场景：销售报表时间维度 ​

下标生成函数 generate_subscripts ​

函数签名 ​

基础数组下标示例 ​

数组遍历和处理 ​

展示数组内容 ​

自定义二维数组展开函数 ​

实际业务场景：标签数组处理 ​

WITH ORDINALITY 子句 ​

基本语法和使用 ​

实际应用场景 ​

场景 1：数据排名编号 ​

场景 2：数组元素索引 ​

性能优化和最佳实践 ​

性能考量 ​

最佳实践总结 ​

常见错误和解决方案 ​

错误 1：步长为零 ​

错误 2：数组维度超出范围 ​

错误 3：NULL 输入处理 ​

总结 ​

PostgreSQL 集合返回函数详解

概述

序列生成函数 generate_series

数值序列生成

函数签名

基础用法示例

实际业务场景应用

时间序列生成

函数签名

时间序列示例

业务场景：销售报表时间维度

下标生成函数 generate_subscripts

函数签名

基础数组下标示例

数组遍历和处理

展示数组内容

自定义二维数组展开函数

实际业务场景：标签数组处理

WITH ORDINALITY 子句

基本语法和使用

实际应用场景

场景 1：数据排名编号

场景 2：数组元素索引

性能优化和最佳实践

性能考量

最佳实践总结

常见错误和解决方案

错误 1：步长为零

错误 2：数组维度超出范围

错误 3：NULL 输入处理

总结