Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT .. INTO to create a table in 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.

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.

like image 784
nnyby Avatar asked Aug 16 '12 00:08

nnyby


People also ask

What is SELECT into in PostgreSQL?

In PostgreSQL, the SELECT INTO statement allows users to create a new table and inserts data returned by a query. The new table columns have names and data types linked with the output columns of the SELECT clause. Unlike the SELECT statement, the SELECT INTO statement does not return data to the client.

Does SELECT into always create a table?

Description. SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT . The new table's columns have the names and data types associated with the output columns of the SELECT .

How do I create a SELECT in PostgreSQL?

PostgreSQL SELECT statement syntaxIf you specify a list of columns, you need to place a comma ( , ) between two columns to separate them. If you want to select data from all the columns of the table, you can use an asterisk ( * ) shorthand instead of specifying all the column names.


1 Answers

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.

like image 59
Kuberchaun Avatar answered Sep 17 '22 21:09

Kuberchaun