临时表 postgresql 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36091047/
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
Temporary table postgresql function
提问by Fran?ois M.
I can't find a clear explanation of the syntax to create (and use) tables just for the inside calculations of a function. Could anyone give me a syntax exemple please ?
我找不到仅用于函数内部计算的创建（和使用）表的语法的明确解释。谁能给我一个语法示例？
From what I've found, I have tried this (with and without @before temp_table) :
根据我的发现，我已经尝试过这个（有和没有@之前temp_table）：
CREATE FUNCTION test.myfunction()
RETURNS SETOF test.out_table
AS $$
DECLARE @temp_table TABLE
(
id int,
value text
)
BEGIN
INSERT INTO @temp_table
SELECT id, value
FROM test.another_table;
INSERT INTO test.out_table
SELECT id, value
FROM @temp_table;
RETURN END
$$ LANGUAGE SQL;
I get :
我得到：
ERROR: syntax error at or near "DECLARE" LINE 5: DECLARE @temp_table TABLE
错误：“DECLARE”第 5 行或附近的语法错误：DECLARE @temp_table TABLE
-
——
I also tried the CREATE TABLE approach suggested here, this way :
我还尝试了此处建议的 CREATE TABLE 方法，如下所示：
CREATE FUNCTION test.myfunction()
RETURNS SETOF test.out_table
AS $$
CREATE TABLE temp_table AS
SELECT id, value
FROM test.another_table;
INSERT INTO test.out_table
SELECT id, value
FROM temp_table;
$$ LANGUAGE SQL;
And I get this :
我明白了：
ERROR: relation "temp_table " does not exist LINE 11: FROM temp_table
错误：关系“temp_table”不存在第 11 行：FROM temp_table
(Obviously, I'm aware the temp_table is not necessary for what I'm doing in the code above, but that's not the point :) => I want to understand the syntax to get it to work)
（显然，我知道 temp_table 对于我在上面的代码中所做的事情不是必需的，但这不是重点:) => 我想了解使其工作的语法）
回答by dizzystar
The appropriate syntax for creating a temp table is
创建临时表的适当语法是
create temp table...
but you have to be sure to drop the temp table before existing out of the function. Also, I'd suggest this syntax instead:
但是您必须确保在退出函数之前删除临时表。另外，我建议改用这种语法：
CREATE TEMP TABLE IF NOT EXISTS temp_table AS
SELECT id, value
FROM test.another_table;
Thus your function will be like this:
因此，您的功能将是这样的：
CREATE FUNCTION test.myfunction()
RETURNS SETOF test.out_table
AS $$
CREATE TEMP TABLE IF NOT EXISTS temp_table AS
SELECT id, value
FROM test.another_table;
INSERT INTO test.out_table
SELECT id, value
FROM temp_table;
DROP TABLE temp_table;
$$ LANGUAGE SQL;
But if I can be so kind, I'd like to rewrite this function so it is more correct:
但如果我可以这么善良，我想重写这个函数，让它更正确：
CREATE FUNCTION test.myfunction()
RETURNS TABLE (id int, value varchar) -- change your datatype as needed
AS $$
BEGIN;
CREATE TEMP TABLE IF NOT EXISTS temp_table AS
SELECT id, value
FROM test.another_table;
INSERT INTO test.out_table
SELECT id, value
FROM temp_table;
DROP TABLE temp_table;
RETURN QUERY
SELECT id, value
from temp_table;
END;
$$ LANGUAGE plpgsql;
Untested; let me know if this fails.
未经测试；如果这失败，请告诉我。
