PostgreSQL 9.5 PL/pgSQL函数查询结果变量类型及字段访问方法

嘿，针对你写PL/pgSQL函数时遇到的查询结果存储和遍历问题，我给你整理了几种实用的方案，按需选择就行：

1. 直接用FOR循环遍历（最省心的方式） #

不用专门声明变量存整个结果集，直接把查询放到FOR循环里，PL/pgSQL会自动帮你逐条遍历记录：

CREATE OR REPLACE FUNCTION get_provider_products(p_id INT, limit_val INT)
RETURNS VOID AS $$
DECLARE
    -- 声明一个记录变量，用来装循环里的单条产品数据
    product_rec RECORD;
BEGIN
    -- 把查询语句直接丢进FOR循环，自动遍历每一条结果
    FOR product_rec IN
        SELECT id, number_of_items 
        FROM product 
        WHERE provider_id = p_id 
          AND total_number > limit_val  -- 注意：limit是PostgreSQL关键字，别用它当参数名哦
    LOOP
        -- 访问字段直接用「变量名.字段名」就行
        RAISE NOTICE '产品ID: %, 库存数量: %', product_rec.id, product_rec.number_of_items;
        
        -- 这里写你要执行的业务逻辑，比如更新库存、计算统计值之类的
    END LOOP;
END;
$$ LANGUAGE plpgsql;

小提醒： #

limit是PostgreSQL的保留关键字，用它当参数名会触发语法错误，建议换成limit_val或者其他名字。

2. 用自定义复合类型存整个结果集 #

如果需要多次复用这个查询的结果结构，可以先定义一个自定义类型，再用数组来存所有结果：

-- 先创建一个自定义复合类型，对应你要查询的字段
CREATE TYPE product_summary AS (
    id INT,
    number_of_items INT
);

-- 然后在函数里用这个类型的数组存结果
CREATE OR REPLACE FUNCTION get_provider_products(p_id INT, limit_val INT)
RETURNS VOID AS $$
DECLARE
    -- 声明自定义类型的数组，用来装所有查询结果
    products product_summary[];
    -- 声明索引变量，用来遍历数组
    i INT;
BEGIN
    -- 把查询结果转成数组存起来
    SELECT ARRAY_AGG(ROW(id, number_of_items)::product_summary) INTO products
    FROM product 
    WHERE provider_id = p_id 
      AND total_number > limit_val;
    
    -- 遍历数组处理每一条数据
    FOR i IN 1..array_length(products, 1) LOOP
        -- 访问数组元素的字段，格式是「数组名[索引].字段名」
        RAISE NOTICE '产品ID: %, 库存数量: %', products[i].id, products[i].number_of_items;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

3. 用游标处理大数据量 #

如果查询结果特别大，一次性加载到内存可能有性能问题，用游标可以逐条获取数据，更节省内存：

CREATE OR REPLACE FUNCTION get_provider_products(p_id INT, limit_val INT)
RETURNS VOID AS $$
DECLARE
    -- 声明一个游标，绑定你的查询语句
    product_cursor CURSOR FOR
        SELECT id, number_of_items 
        FROM product 
        WHERE provider_id = p_id 
          AND total_number > limit_val;
    -- 记录变量，用来装游标返回的单条数据
    product_rec RECORD;
BEGIN
    -- 打开游标
    OPEN product_cursor;
    
    -- 循环从游标里拿数据，直到没有更多记录
    LOOP
        FETCH product_cursor INTO product_rec;
        EXIT WHEN NOT FOUND; -- 没数据就退出循环
        
        -- 访问字段的方式和之前一样
        RAISE NOTICE '产品ID: %, 库存数量: %', product_rec.id, product_rec.number_of_items;
    END LOOP;
    
    -- 记得关闭游标
    CLOSE product_cursor;
END;
$$ LANGUAGE plpgsql;

总结 #

• 如果只是简单遍历查询结果，首推第一种FOR ... IN SELECT的方式，代码最简洁，不用自己管游标和内存的问题。
• 不管用哪种方式，访问记录字段的格式都是统一的：变量名.字段名（数组的话就是数组[索引].字段名）。

内容的提问来源于stack exchange，提问作者Siavosh