postgresql - 如何创建返回多行的 PL/pgSQL 函数

Question

我正在尝试创建 a PL/pgSQL function ，它应该填充一个临时表，然后从中返回所有行(稍后将是一个连接)，但我不知道为它指定哪种返回类型:

create or replace function pref_daily_misere() returns void as $BODY$
        begin

        create temporary table temp_best (id varchar not null) on commit drop;
        insert into temp_best (id) select id from pref_money where
            yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW')
            order by money desc limit 10;
        select id from temp_best;
        end;
$BODY$ language plpgsql;

上面的语句自己工作，但给我错误

# select pref_daily_misere();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "pref_daily_misere" line 7 at SQL statement

当我尝试在我的 PostgreSQL 8.4.11 数据库中调用它时。

这可能是因为我错误地指定了上面的returns void，但我不知道应该使用哪种返回类型，省略返回类型是一个编译错误。

Answer 1

您想在函数内部使用 setof varchar 返回类型，然后使用 return query ...。来自fine manual :

39.6.1.2. RETURN NEXT and RETURN QUERY

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing.

我想你想要更像这样的东西:

create or replace function pref_daily_misere() returns setof varchar as $BODY$
begin
    create temporary table temp_best (id varchar not null) on commit drop;
    insert into temp_best (id)
    select id
    from pref_money
    where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW')
    order by money
    desc limit 10;
    return query select id from temp_best;
    return;
end;
$BODY$ language plpgsql;

但是，临时表在这里毫无意义:

Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above.

因此 PostgreSQL 正在计算整个结果集并自行缓存。你可以这样做:

create or replace function pref_daily_misere() returns setof varchar as $BODY$
begin
    return query
        select id
        from pref_money
        where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW')
        order by money
        desc limit 10;
    return;
end;
$BODY$ language plpgsql;

我很确定临时表无论如何都会在你的函数结束时被删除，所以你应该摆脱它。