postgresql 退出函数时删除临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32998139/
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
Drop temporary table when exiting a function
提问by Thomas Perrin
I use a temp table in a function with the 'on commit drop' option. My problem is, in certain cases, a more global function can call the first one twice, so the "create temp table" is called twice before the commit - so I have the normal error "relation [my_temp_table] already exists".
我在一个带有“on commit drop”选项的函数中使用了一个临时表。我的问题是，在某些情况下，一个更全局的函数可以调用第一个函数两次，所以在提交之前“创建临时表”被调用两次 - 所以我有正常的错误“关系 [my_temp_table] 已经存在”。
I use the temp table at the end of the function to return its rows in the "return query", so I can't manually drop the table before I leave the function.
我使用函数末尾的临时表在“返回查询”中返回其行，因此在离开函数之前我无法手动删除该表。
CREATE OR REPLACE FUNCTION my_function(_value text)
RETURNS setof my_table AS $$
DECLARE
resultCount integer := 0;
BEGIN
create temp table my_temp_table on commit drop as
select *
from my_table
where value = _value ;
select count(*) into resultCount from my_temp_table;
if (resultCount = 0) then
raise exception 'value not found';
end if;
return query
select * from my_temp_table;
END;$$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION my_function(text) OWNER TO postgres
If you wonder why I use a temp table and not my_table in a direct way it's because I need a really fast response and my_table is very big (several dozens of millions rows) so this way I can request it only once instead of three times (search, count and return).
如果您想知道为什么我直接使用临时表而不是 my_table，那是因为我需要非常快速的响应，而 my_table 非常大（几千万行），所以这样我只能请求一次而不是三次（搜索、计数和返回）。
I found a workaround not using temp table and creating a type, but the structure of my_table will change many times, and actually I have dozens of "my table" and the concerned "my function", so this was a way to not write again all my functions each time the structure of my tables will change.
我找到了一个不使用临时表并创建类型的解决方法，但是my_table的结构会改变很多次，实际上我有几十个“我的表”和相关的“我的函数”，所以这是一种不再写的方法每次我的表格结构都会改变时，我的所有功能。
The function has to return the same structure than the table it requests.
该函数必须返回与其请求的表相同的结构。
How can I can drop the table when leaving the function? Or is there a better workaround?
离开函数时如何删除表？或者有更好的解决方法吗？
回答by Gabriel's Messanger
... return its rows in the "return query", so I can't manually drop the table before I leave the function.
...在“返回查询”中返回它的行，所以我不能在离开函数之前手动删除表。
Actually you can. You can use several RETURN QUERYin your case.
After manual:
其实你可以。您可以RETURN QUERY在您的情况下使用多个。手动后：
When a PL/pgSQL function is declared to return SETOF [...] 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
当一个 PL/pgSQL 函数被声明为返回 SETOF [...] 时，要返回的单个项由一系列 RETURN NEXT 或 RETURN QUERY 命令指定，然后最后一个没有参数的 RETURN 命令用于指示该函数执行完毕
So you could do:
所以你可以这样做：
RETURN QUERY
SELECT * FROM my_temp_table;
DROP TABLE my_temp_table;
RETURN;
回答by klin
You could drop the table just in case:
您可以删除表格以防万一：
...
BEGIN
drop table if exists my_temp_table;
create temp table my_temp_table on commit drop as
....
but... in fact you do not need a temporary table. Try this code:
但是...实际上您不需要临时表。试试这个代码：
...
return query
select *
from my_table
where value = _value ;
if not found then
raise exception 'value not found';
end if;
...
