Hi Tom, I do read the doc, and understand the caching behaviour of plpgsql. if in psql I write begin;execute moninsert(randname()); execute moninsert(randname());end; it does work. And if I put this (begin execute end) inside a do loop it doesnt anymore. ok the begin execute end is ""pure"" SQL, and the same thing within a do loop is plpgsql so postgres=# create function testexec()returns void as $$ postgres$# execute moninsert(randname()); postgres$# end; postgres$# $$ language plpgsql; ERREUR: erreur de syntaxe sur ou près de « execute » LIGNE 2 : execute moninsert(randname()); fine, quite coherent. then postgres=# create function testexec()returns void as $$ execute moninsert(randname()); end; $$ language sql; CREATE FUNCTION as SQL, legal syntax.. ok but postgres=# select testexec(); ERREUR: COMMIT n'est pas autorisé dans une fonction SQL CONTEXTE : fonction SQL « testexec » lors du lancement a bit more difficult to understand, as such.(where is the commit ??) so.. the prepare//execute thing can only be used in embedded SQL (as not in any plpg, nor in sql functions. The doc states : The SQL standard includes a PREPARE statement, but it is only for use in embedded SQL. This version of the PREPARE statement also uses a somewhat different syntax. ??? where is the difference for the prepare context thing (I dont mean the different syntax part) ??
thanks for clarification Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Mon, Feb 15, 2021 at 5:27 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Marc Millas <marc.mil...@mokadb.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 >
