From: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: prepare in a do loop |
Date: | 2021-02-15 17:55:26 |
Message-ID: | CADX_1aZqb8T0heF_ROEiMCn3oM5n8a_up66aZWj8_T7JBV_wYA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-02-15 18:01:00 | Re: certs in connection string |
Previous Message | Paolo Saudin | 2021-02-15 17:55:14 | Replication sequence |