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

plpgsql, postgresql, postgresql-9.1

Solution

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.

Problem

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.

Original source