PostgreSQL基于私有表返回表的PL/pgSQL函数报错如何修复

原代码存在5处核心错误，导致无法正常运行：

• 局部变量声明不符合PL/pgSQL语法规则：PL/pgSQL不支持直接用table(列定义)格式声明局部表类型变量，该写法会直接触发语法解析错误。
• INSERT语句语序完全错误：标准写入语法为INSERT INTO 目标表(字段列表) SELECT 取值逻辑 FROM 源表，原代码把SELECT子句写在INSERT关键字前，还将列别名插在INSERT语句中间，属于基础语法错误，数据库无法解析执行。
• 字段类型不匹配：floor()函数默认返回numeric类型，和函数定义中要求返回的int类型不兼容，执行时会抛出类型转换错误。
• 权限配置缺失：prices_ranges是开启了行级安全（RLS）的私有表，且未配置公共访问策略，函数默认以调用者权限（SECURITY INVOKER）执行，普通调用用户没有该表的查询权限，就算语法正确也读不到数据。
• 返回逻辑错误：PL/pgSQL的表返回函数不支持直接return 表名的写法，无法将表结构变量直接作为结果集返回。

优先选择无中间表的简洁写法，性能更高、代码更易维护，核心是修正语法、补全权限配置、做显式类型转换：

create or replace function get_random_prices()
returns table (item_id int, value int)
as $$
begin
    -- 直接通过RETURN QUERY返回查询结果，不需要额外中间表存储
    return query
    select 
        pr.id::int,
        floor(random() * (pr.max_value - pr.min_value + 1) + pr.min_value)::int
    from prices_ranges pr;
end
$$ language plpgsql
-- 关键配置：以函数创建者身份执行，让无私有表权限的公共用户也能获取计算结果
security definer
-- 安全配置：固定搜索路径，避免security definer模式被恶意利用提权
set search_path = public;

补充说明 #

如果业务逻辑必须先把数据存入中间表做二次加工，不能直接返回查询结果，可以用临时表替代错误的table类型变量，参考写法如下：

create or replace function get_random_prices()
returns table (item_id int, value int)
as $$
begin
    -- 创建会话级临时表，事务提交后自动删除，避免残留数据
    create temporary table if not exists random_prices_tmp (
        item_id int,
        value int
    ) on commit drop;

    -- 修正INSERT语句语序，写入计算后的随机价格
    insert into random_prices_tmp (item_id, value)
    select 
        pr.id::int,
        floor(random() * (pr.max_value - pr.min_value + 1) + pr.min_value)::int
    from prices_ranges pr;

    -- 返回临时表中存储的结果
    return query select * from random_prices_tmp;
end
$$ language plpgsql
security definer
set search_path = public;

注意：使用security definer配置前，需要确保函数的创建用户本身拥有prices_ranges表的查询权限，否则依然无法读取私有表数据。

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