Re: prepare in a do loop

Marc Millas <marc(dot)millas(at)mokadb(dot)com> writes:
> in psql, with a postgres 12.5 db on a centos 7 intel:
> I do create a function named randname() returning a varchar, and a table
> matable with a column prenom varchar(50). then
> postgres=# prepare moninsert(varchar) as
> postgres-# insert into matable(prenoms) values($1);
> PREPARE

> I test it:
> postgres=# execute moninsert(randname());
> INSERT 0 1

> up to now, everything fine. then:
> do $$ begin for counter in 1..1000000 loop execute
> moninsert(randname());end loop;end;$$;
> ERREUR: la fonction moninsert(character varying) n'existe pas
> LIGNE 1 : SELECT moninsert(randname())

> someone can explain ?

EXECUTE in plpgsql has nothing whatever to do with the SQL EXECUTE
command. See the respective documentation.

You don't actually need to use SQL PREPARE/EXECUTE in plpgsql.
If you just write "insert into ..." as a command in a
plpgsql function, it's automatically prepared behind the scenes.
Indeed, one of the common uses for plpgsql's EXECUTE is to stop
a prepared plan from being used when you don't want that ... so
far from being the same thing, they're more nearly opposites.
Perhaps a different name should have been chosen, but we're
stuck now.

regards, tom lane