Temp table inside a Procedure in Postgres

Question

I am trying to create and populate a temp table inside a procedure to save some intermediate state of the data I am working with.

I have created an example code to explain what I am trying to do:

CREATE OR REPLACE PROCEDURE etl.my_test_procedure()
LANGUAGE sql
AS 
$$
   CREATE TEMP TABLE IF NOT EXISTS my_temp(
       var1 VARCHAR(255),
       var2 VARCHAR(255)
   ) ON COMMIT DROP;
    
   INSERT INTO my_temp (
       var1,
       var2
   )
   SELECT 
       table_schema,
       column_name
   FROM information_schema.columns;

   SELECT 
        *
   FROM my_temp
$$

When trying to create this Stored Procedure the database returns this error message: ERROR: relation "my_temp" does not exist LINE 10: INSERT INTO my_temp ( ^ SQL state: 42P01 Character: 171

PD: My version of Postgres is 13.3

Answer 1

You would have to use plpgsql instead of sql

CREATE OR REPLACE FUNCTION my_test_procedure()
RETURNS TABLE(var1 VARCHAR(255), var2 VARCHAR(255))
AS 
$$
 DECLARE

  BEGIN

   CREATE TEMP TABLE IF NOT EXISTS my_temp(
       var1 VARCHAR(255),
       var2 VARCHAR(255)
   ) ON COMMIT DROP;
    
   INSERT INTO my_temp (
       var1,
       var2
   )
   SELECT 
       table_schema,
       column_name
   FROM information_schema.columns;

   RETURN QUERY SELECT *
   FROM my_temp;

  END;
$$ LANGUAGE plpgsql;