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;

Answer 2

The reason for the error is that SQL functions are parsed when they are created. You can avoid that by setting the parameter check_function_bodies to off.

But that doesn't help you much: it allows you to create the function, but you will end up with the same error when you execute the procedure, since all statements are parsed when the function starts, and my_temp does not exist at that time.

The solution is to use PL/pgSQL, like JGH's answer suggests.