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.

Blacksmith

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.

Towering

Thanks! I knew I had solved this problem before, and I remember that sentence from the postgresql docs. I just had totally forgotten about CREATE TABLE AS. – Blacksmith