prepared statement "fooplan" already exists

user_15120695 0
Viewed 439

Running the following SQL statement:

 create or replace procedure test_insert_prepare2(howmany int)
        language plpgsql 
        as $$
        declare counter integer:=0;
        declare note int;
        begin
        prepare fooplan(int) as 
        insert into procedure_testing_table values($1, $2);
        select count(*) into strict note from procedure_testing_table;
        while counter < howmany loop
        counter := counter +1;
        note := note +1;
        execute fooplan(note,'testing_prepare');
        end loop;
        end;
        $$

always shows an error:

ERROR:  prepared statement "fooplan" already exists

How could I use prepare statement correctly in a while loop?

postgresql
1 Answers

Prepared statements live until the end of the database session or until you explicitly deallocate them, so the second invocation of the function will cause this error.

But it is not necessary to use a prepared statement here, because PL/pgSQL automatically caches execution plans. So use a plain INSERT inside the loop, it will be just as efficient.

user_64643080
Related