SELECT .. INTO to create a table in PL/pgSQL

Question

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.

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

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

CREATE OR REPLACE FUNCTION whatever() RETURNS void AS $$ BEGIN     SELECT *     INTO TEMP TABLE mytable     FROM orig_table; END; $$ LANGUAGE plpgsql; 

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?

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.

Answer 1

Try

CREATE TEMP TABLE mytable AS SELECT * FROM orig_table; 

Per 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.