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

The temp table is completely useless. Just return the result of the select — , Sep 07 '21 at 18:05

score 4 · Accepted Answer · answered Sep 07 '21 at 18:05

4

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;

answered Sep 07 '21 at 18:05

JGH

15,928
4
31
48

Thank you very much! Just a little follow-up. I am not actually interested on get that table as an output from the procedure (function?) but it will be use as the input to get another intermediate result till at the end of the line some rows will be inserted into a final table. So, I guess, RETURNS should void (RETURNS void), isn't it? Also, why are you using FUNCTION instead of PROCEDURE? any documentation could help me to understand that? PD: my background is SqlServer and this is a lot simpler on that environment... – Antjes Sep 08 '21 at 08:23

score 1 · Answer 2 · answered Sep 08 '21 at 06:20

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.

Temp table inside a Procedure in Postgres

2 Answers2

Linked

Related