PostgreSQL返回表类型函数报错：未指定结果返回目标的排查与解决

问题场景 #

我之前在PostgreSQL里写了一个返回TABLE类型的分页函数public.scrolltable，结果执行的时候直接报错：

ERROR: The result data return destination is not specified in the query

原函数代码是这样的：

CREATE OR REPLACE FUNCTION public.scrolltable( IN p_pagenumber integer, IN p_pagesize integer) 
RETURNS TABLE(name character varying, id character varying, rn bigint) 
AS $BODY$ 
Declare 
    v_startRow int; 
    v_endRow int;
Begin 
    v_startRow := (p_pagenumber - 1) * p_pagesize + 1; 
    v_endRow := p_pagenumber * p_pagesize; 
    With Result as ( 
        Select *, ROW_NUMBER() OVER (Order By "ID" ASC) From "Training" 
    ) 
    select * from Result Where row_number Between v_startRow AND v_endRow; 
End; 
$BODY$ LANGUAGE plpgsql

有意思的是，单独把函数里的查询语句拿出来执行，是能得到和返回类型结构完全匹配的结果的：

------------------------------------------------------------------------
| <Name>                  | <ID>                    | <row_number>       |
| <character varying(100)>| <character varying(100)>| <bigint>           |
------------------------------------------------------------------------

我反复确认了返回类型和查询结果的结构是一致的，但报错还是没消失，最后才找到解决办法。

问题原因 #

其实这是PL/pgSQL的一个基础规则：当函数声明返回TABLE或者SETOF类型时，你不能只在函数里写SELECT语句就完事了。PL/pgSQL需要你明确告诉它——这个查询的结果就是要返回给调用者的数据，不然它不知道该怎么处理这个查询的输出，就会抛出那个返回目标未指定的错误。

修复方法 #

只需要在函数里的查询语句前面加上RETURN QUERY关键字就行，这样PL/pgSQL就明白要把后续查询的结果作为函数的返回值输出了。修复后的代码如下：

CREATE OR REPLACE FUNCTION public.scrolltable( IN p_pagenumber integer, IN p_pagesize integer) 
RETURNS TABLE(name character varying, id character varying, rownumber bigint) 
AS $BODY$ 
Declare 
    v_startRow int; 
    v_endRow int;
Begin 
    v_startRow := (p_pagenumber - 1) * p_pagesize + 1; 
    v_endRow := p_pagenumber * p_pagesize; 
    RETURN QUERY
    With Result as ( 
        Select *, ROW_NUMBER() OVER (Order By "ID" ASC) From "Training" 
    ) 
    select * from Result Where row_number Between v_startRow AND v_endRow; 
End; 
$BODY$ LANGUAGE plpgsql

这样修改之后，函数就能正常返回分页后的结果了。

内容的提问来源于stack exchange，提问作者Nguyễn Duy Hàn Lâm