PostgreSQL创建返回查询表的存储过程报TABLE语法错误如何解决？

问题原因 #

1. PostgreSQL的PROCEDURE（存储过程）不支持RETURNS TABLE语法，也不能通过RETURN QUERY直接返回查询结果集，该特性仅FUNCTION（函数）支持。存储过程的设计定位是执行事务操作、数据变更类逻辑，不面向结果集返回场景。
2. 语法冗余错误：你代码中RETURNS TEMP TABLE的TEMP关键字是多余的，定义返回表结构不需要加临时表标识。
3. 原代码中RETURN QUERY对应的SELECT语句结尾缺少分号，也会导致执行报错。

修正后可运行代码 #

你需要将存储过程改为函数实现，修改后代码如下：

CREATE OR REPLACE FUNCTION "sap_data"."get_emp_number"(dep VARCHAR)
     RETURNS TABLE (
                    emp_id VARCHAR,
                    department VARCHAR)
     AS $BODY$
     BEGIN
        RETURN QUERY SELECT
            emp_id,
            department
        FROM
            emp_data
        WHERE
            department = dep;
    END;$BODY$
      LANGUAGE plpgsql;

调用方法 #

要获取过滤后的查询结果，直接通过查询语句调用函数即可：

SELECT * FROM "sap_data"."get_emp_number"('目标部门名称');

如果你有必须使用存储过程的特殊场景，可以通过INOUT refcursor类型参数传递结果游标实现，调用时需要额外处理游标获取数据，对于简单查询返回场景优先推荐使用上述函数方案。

内容的提问来源于stack exchange，提问作者Minh Tô Hà