postgresql - RETURN 不能在函数返回集中有参数；在 "QUERY"Postgres 处或附近使用 RETURN NEXT

Question

当我尝试编译这个函数时:

CREATE OR REPLACE FUNCTION test_proc4(sr_num bigint) 
    RETURNS TABLE(sr_number bigint, product_serial_number varchar(35))
AS $$
    BEGIN
        RETURN QUERY SELECT select sr_number,product_serial_number from temp_table where sr_number=sr_num
    END;
$$
LANGUAGE 'plpgsql' VOLATILE;

为什么会出现此错误？

RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY"

我使用的是 postgres 8.4 版。

Answer 1

除了打字错误(您重复了 select，并且没有用分号终止 RETURN 语句)，我认为您非常接近 - 只需要通过用表名限定它们来消除查询中的表列的歧义。试试这个(希望重新格式化以提高可读性):

CREATE OR REPLACE FUNCTION test_proc4(sr_num bigint)
    RETURNS TABLE(sr_number bigint, product_serial_number varchar(35)) AS $$
  BEGIN
    RETURN QUERY
      SELECT
        temp_table.sr_number, temp_table.product_serial_number
      from temp_table
      where temp_table.sr_number=sr_num;
  END;
$$ LANGUAGE 'plpgsql' VOLATILE;

警告:我只在 PG 9.4 中测试过它，所以还没有在你的版本中测试过它(不再支持，我可能会补充)。如果您的版本和 9.4 之间存在有关 PLPGSQL 实现的问题，您可以尝试使用此表单作为替代:

CREATE OR REPLACE FUNCTION test_proc4(sr_num bigint)
    RETURNS TABLE(sr_number bigint, product_serial_number varchar(35)) AS $$
  BEGIN
    FOR sr_number, product_serial_number IN
      SELECT
        temp_table.sr_number, temp_table.product_serial_number
      from temp_table
      where temp_table.sr_number=sr_num
    LOOP
      RETURN NEXT;
    END LOOP;
    RETURN;
  END;
$$ LANGUAGE 'plpgsql' VOLATILE;

使用我填充了虚拟数据的表进行的小型健全性检查:

postgres=# select * from temp_table;
 sr_number | product_serial_number
-----------+-----------------------
         1 | product 1
         2 | product 2
         2 | another product 2
(3 rows)

postgres=# select * from test_proc4(1);
 sr_number | product_serial_number
-----------+-----------------------
         1 | product 1
(1 row)

postgres=# select * from test_proc4(2);
 sr_number | product_serial_number
-----------+-----------------------
         2 | product 2
         2 | another product 2
(2 rows)