SELECT .. INTO to create a table in PL/pgSQL
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.
nnyby
Updated on March 09, 2020Comments
-
nnyby about 4 years
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 typerecord
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 theSELECT
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.