PostgreSQL 在 plpgsql 函数中创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10099230/
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
PostgreSQL CREATE TEMPORARY TABLE inside a plpgsql function
提问by Dan Walmsley
I am trying to create a function that does this:
我正在尝试创建一个执行此操作的函数：
drop table t_rv_openitem;
select * into t_rv_openitem from rv_openitem;
select * from t_rv_openitem;
I am confused sometimes when it comes to functions in PostgreSQL and get this error:
当谈到 PostgreSQL 中的函数时，我有时会感到困惑并收到此错误：
An error has occurred:
ERROR: syntax error at or near "DROP" LINE 3: DROP TABLE t_rv_openitem;
发生了错误：
错误：“DROP”第 3 行或附近的语法错误：DROP TABLE t_rv_openitem；
I know this seems like a simple task but I am pulling my hair out trying to figure this out.
我知道这似乎是一项简单的任务，但我正在努力解决这个问题。
Here is the full function create statement:
这是完整的函数 create 语句：
CREATE OR REPLACE FUNCTION adempiere.update_t_rv_openitem()
RETURNS rv_openitem AS
$BODY$
Drop table t_rv_openitem;
select * into t_rv_openitem from rv_openitem;
select * From t_rv_openitem;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION adempiere.update_t_rv_openitem() OWNER TO adempiere;
采纳答案by Michael Buen
Just add BEGIN and END
只需添加 BEGIN 和 END
CREATE OR REPLACE FUNCTION adempiere.update_t_rv_openitem()
RETURNS rv_openitem AS
$BODY$
BEGIN -- ADD THIS
Drop table t_rv_openitem;
select * into t_rv_openitem from rv_openitem;
select * From t_rv_openitem;
END; -- AND THIS
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION adempiere.update_t_rv_openitem() OWNER TO adempiere;
You don't need BEGIN and END block if you are using LANGUAGE sql, you need those though if you are using LANGUAGE plpgsql
如果您正在使用LANGUAGE sql，则不需要 BEGIN 和 END 块，但如果您正在使用，则需要它们LANGUAGE plpgsql
UPDATE
更新
Regarding ERROR: syntax error at "t_rv_openitem" DETAIL: Expected record variable.... There's no syntax error on your code, you just need to change this:
关于ERROR: syntax error at "t_rv_openitem" DETAIL: Expected record variable.... 你的代码没有语法错误，你只需要改变这个：
select * into t_rv_openitem from rv_openitem;
To this:
对此：
create table t_rv_openitem as
select * from rv_openitem;
The table creation using SELECT * INTO tablehere FROM tableSourceworks only if you are using it outside of PLPGSQL; when that code struct is inside PLPGSQL, the semantic will be different, it means:
SELECT * INTO tablehere FROM tableSource仅当您在 PLPGSQL 之外使用它时，表创建才有效；当该代码结构在 PLPGSQL 内部时，语义会有所不同，这意味着：
SELECT * INTO declaredVariableHere FROM tableSource;
To make table creation work on both stand-alone statement and inside PLPGSQL, just use:
要在独立语句和 PLPGSQL 内部创建表，只需使用：
CREATE TABLE AS SELECT * FROM tableSourceHere;
