CODEWITHSUNDEEP
postgresql
101010
101010

Reputation: 15716

How to use SELECT INTO in a plpgsql PROCEDURE?

I'm trying to use SELECT INTO in a plpgsql procedure.

CREATE OR REPLACE FUNCTION reset_data()
RETURNS void AS $$
BEGIN
DROP TABLE IF EXISTS experiment;
SELECT * INTO experiment FROM original;
END;
$$ LANGUAGE plpgsql;

This results in an error: 

ERROR:  "experiment" is not a known variable
LINE 5: SELECT * INTO experiment FROM original;
                      ^

********** Error **********

ERROR: "experiment" is not a known variable
SQL state: 42601
Character: 113

Apparently, we cannot use SELECT INTO like this. How do we do it?

Upvotes: 0

Views: 1370

Answers (2)

Astisformen
Astisformen

Reputation: 1

I believe this question deserves a better answer even though lots of time has passed. In order to use the SELECT INTO syntax properly, you need to declare your variable first and then assign it a value. Here's an example.

CREATE OR REPLACE FUNCTION reset_data()
 RETURNS void
 LANGUAGE plpgsql
AS $$
DECLARE
   original_extraction record; 
BEGIN
   -- use the SELECT INTO syntax and extract a record
   SELECT *
   INTO original_extraction 
   FROM original
   LIMIT 1;

   -- do whatever with your record
END; $$

By declaring the variable, you'll be able to resolve the error is not a known variable

Upvotes: 0

user330315
user330315

Reputation:

It's discouraged to use select into to create a table based on a select statement.

It is recommended to use the (standard compliant) create table as.

The documentation for select into explicitly mentions PL/pgSQL as one of the reasons:

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

So your function should be:

CREATE OR REPLACE FUNCTION reset_data()
RETURNS void AS $$
BEGIN
  DROP TABLE IF EXISTS experiment;
  create table experiment 
  as 
  SELECT * FROM original;
END;
$$ LANGUAGE plpgsql;

Upvotes: 3

Related Questions