SELECT .. INTO在PL/pgSQL中创建表 [英] SELECT .. INTO to create a table in PL/pgSQL

问题描述

我想使用SELECT INTO在我的其中一个函数中创建一个临时表. SELECT INTO在SQL中有效，但在PL/pgSQL中无效.

I want to use SELECT INTO to make a temporary table in one of my functions. SELECT INTO works in SQL but not PL/pgSQL.

此语句创建一个名为mytable的表(如果orig_table作为关系存在):

This statement creates a table called mytable (If orig_table exists as a relation):

SELECT *
INTO TEMP TABLE mytable
FROM orig_table;

但是将此函数放到PostgreSQL中，会出现错误:ERROR: "temp" is not a known variable

But put this function into PostgreSQL, and you get the error: 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;

我可以在PL/pgSQL中SELECT INTO类型为record的变量，但是当我从记录中获取数据时，我必须定义结构. SELECT INTO非常简单-自动创建与SELECT查询相同结构的表.有谁对为什么在函数内部不起作用有任何解释?

I can SELECT INTO a variable of type record within PL/pgSQL, but then I have to define the structure when getting data out of that record. SELECT INTO is really simple - automatically creating a table of the same structure of the SELECT query. Does anyone have any explanation for why this doesn't work inside a function?

SELECT INTO在PL/pgSQL中的工作方式似乎有所不同，因为您可以选择已声明的变量.不过，我不想声明我的临时表结构.我希望它会像在SQL中一样自动创建结构.

It seems like SELECT INTO works 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.

推荐答案

尝试

CREATE TEMP TABLE mytable AS
SELECT *
FROM orig_table;

每个 http://www.postgresql.org/docs/current /static/sql-selectinto.html

CREATE TABLE AS在功能上类似于SELECT INTO.推荐使用CREATE TABLE AS语法，因为这种形式的SELECT INTO在ECPG或PL/pgSQL中不可用，因为它们对INTO子句的解释不同.此外，CREATE TABLE AS提供了SELECT INTO提供的功能的超集.

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.

这篇关于SELECT .. INTO在PL/pgSQL中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！