postgresql 在plpgsql中，如何退出返回记录的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8707978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
in plpgsql, how to exit from a function returning records
提问by cc young
in postgresql plpgsql,
在 postgresql plpgsql 中，
create function f1( p_i int ) returns table( c1 int ) as $$
begin
-- wish to exit, do not wish to return anything
if p_i < 0 then
-- cannot RETURN - since can only return record!
end if;
-- continue processing
return query select c2 from t1 where c1 = p_i;
...
end;
$$ language plpgsql;
according to doc, the only way to break out of a function is RETURN. but a RETURN here requires RETURN QUERY or RETURN NEXT - there seems to be no way to simply exit out of the function.
根据doc，跳出函数的唯一方法是返回。但是这里的 RETURN 需要 RETURN QUERY 或 RETURN NEXT - 似乎没有办法简单地退出函数。
采纳答案by mu is too short
If p_i < 0is actually an error then you could raise an exception:
如果p_i < 0实际上是一个错误，那么您可以引发异常：
if p_i < 0 then
raise exception 'Don''t know what to do with %', p_i
end if;
If p_i < 0should just quietly return nothing then you could do something like this:
如果p_i < 0只是安静地不返回任何内容，那么您可以执行以下操作：
create or replace function f1( p_i int ) returns table( c1 int ) as $$
begin
if p_i < 0 then
return;
end if;
return query select c2 from t1 where c1 = p_i;
end;
$$ language plpgsql;
From the fine manual:
从精美的手册：
39.6.1.2. RETURN NEXT and RETURN QUERY
[...]
the individual items to return are specified by a sequence ofRETURN NEXTorRETURN QUERYcommands, and then a finalRETURNcommand with no argument is used to indicate that the function has finished executing.
39.6.1.2. RETURN NEXT 和 RETURN QUERY
[...]
要返回的单个项由一系列RETURN NEXT或RETURN QUERY命令指定，然后使用不带参数的最终RETURN命令来指示函数已完成执行。
Emphasis mine. So you can use your return queryto return the query and just a simple return;to bail out without doing anything.
强调我的。因此，您可以使用您的return query来返回查询，return;而无需执行任何操作即可轻松退出。
For example, the return;version gives me things like this:
例如，该return;版本给了我这样的东西：
=> select * from f1(-1);
c1
----
(0 rows)
=> select * from f1(1);
c1
----
1
1
...
(15 rows)
and the exception version does this:
并且异常版本这样做：
=> select * from f1(-1);
ERROR: Don't know what to do with -1
