Thread: prepare in a do loop
Hi,
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 ?
thanks
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 ?
thanks
(its a french db, so error message in french :-)
On 2/15/21 8:18 AM, Marc Millas wrote: > Hi, > > 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 means something different: https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > thanks > > (its a french db, so error message in french :-) > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com <http://www.mokadb.com> > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Feb 15, 2021 at 9:19 AM Marc Millas <marc.millas@mokadb.com> wrote:
postgres=# prepare moninsert(varchar) as
do $$ begin for counter in 1..1000000 loop execute moninsert(randname());end loop;end;$$;
ERREUR: la fonction moninsert(character varying) n'existe pas
someone can explain ?
From the pl/pgsql docs:
"The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL statement supported by the PostgreSQL server. The server's EXECUTE statement cannot be used directly within PL/pgSQL functions (and is not needed)."
David J.
Marc Millas <marc.millas@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
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());
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
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
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
On Mon, Feb 15, 2021 at 5:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marc Millas <marc.millas@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
On 2/15/21 9:55 AM, Marc Millas wrote: > Hi Tom, > > I do read the doc, and understand the caching behaviour of plpgsql. This is not about plpgsql caching. It is about EXECUTE in plpgsql meaning something different then the PREPARE/EXECUTE combination in SQL. You are trying to run EXECUTE moninsert(randname()) in plpgsql where moninsert was a PREPARE statement. In plpgsql EXECUTE is something different so it does not recognize moninsert(randname()) as a prepared statement and fails. > 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 -- Adrian Klaver adrian.klaver@aklaver.com