postgresql SELECT .. INTO 在 PL/pgSQL 中创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11979154/
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
SELECT .. INTO to create a table in PL/pgSQL
提问by nnyby
I want to use SELECT INTOto make a temporary table in one of my functions. SELECT INTOworks in SQL but not PL/pgSQL.
我想用来SELECT INTO在我的一个函数中创建一个临时表。SELECT INTO适用于 SQL 但不适用于 PL/pgSQL。
This statement creates a table called mytable (If orig_tableexists as a relation):
此语句创建一个名为 mytable 的表（如果orig_table作为关系存在）：
SELECT *
INTO TEMP TABLE mytable
FROM orig_table;
But put this function into PostgreSQL, and you get the error: ERROR: "temp" is not a known variable
但是把这个函数放到 PostgreSQL 中，你会得到错误： ERROR: "temp" is not a known variable
CREATE OR REPLACE FUNCTION whatever()
RETURNS void AS $$
BEGIN
SELECT *
INTO TEMP TABLE mytable
FROM orig_table;
END; $$ LANGUAGE plpgsql;
I can SELECT INTOa variable of type recordwithin PL/pgSQL, but then I have to define the structure when getting data out of that record. SELECT INTOis really simple - automatically creating a table of the same structure of the SELECTquery. Does anyone have any explanation for why this doesn't work inside a function?
我可以在 PL/pgSQL 中使用SELECT INTO一个类型的变量record，但是当从该记录中获取数据时，我必须定义结构。SELECT INTO真的很简单——自动创建一个与SELECT查询结构相同的表。有没有人解释为什么这在函数内不起作用？
It seems like SELECT INTOworks differently in PL/pgSQL, because you can select into the variables you've declared. I don't want to declare my temporary table structure, though. I wish it would just create the structure automatically like it does in SQL.
它SELECT INTO在 PL/pgSQL 中的工作方式似乎有所不同，因为您可以选择已声明的变量。不过，我不想声明我的临时表结构。我希望它能像在 SQL 中一样自动创建结构。
回答by Kuberchaun
Try
尝试
CREATE TEMP TABLE mytable AS
SELECT *
FROM orig_table;
Per http://www.postgresql.org/docs/current/static/sql-selectinto.html
每个http://www.postgresql.org/docs/current/static/sql-selectinto.html
CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.
CREATE TABLE AS 在功能上类似于 SELECT INTO。CREATE TABLE AS 是推荐的语法，因为这种形式的 SELECT INTO 在 ECPG 或 PL/pgSQL 中不可用，因为它们对 INTO 子句的解释不同。此外，CREATE TABLE AS 提供了 SELECT INTO 提供的功能的超集。
