On 12/14/21 11:30, Bryn Llewellyn wrote:
/Adrian Klaver wrote:/
/Bryn wrote:/
Thanks for the links to the articles on the inlining of “language sql”
functions into SQL statements that use them. (I noted “the exact
conditions which apply to inlining are somewhat complex and not well
documented outside the source code” in the PG Wiki.) This optimization
is interesting. But its discussion is orthogonal to the question that I
asked.
You asked:
"There must be a reason to prefer a “language sql” procedure over a
“language plpgsql” procedure—otherwise the former wouldn’t be supported."
I provided two reasons, or did I misread that?
It would seem, on its face, that the DO block is preferable because it
uses a single server call from the client rather than four. (Maybe it’s
two server calls if the implementation of autocommit is done client-side
by sending a follow-up “commit”.) I do know that at least some client
languages that have a PG driver allow many SQL statements to be sent in
a single call. I’ve heard that psql will do this if all the statements
are on one line. But I can’t find anything in the PG docs about this. Is
it true? And if so, where is it documented? However, this just feels far
less like a clear way to ask for what you want than a DO block. And it
would lead to unreadable code with only a small number of to-be-batched
SQL statements.
Seems to work for the tests:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/timestamp.sql;h=e011e779ea2da20393f624505ad6dea7f9582438;hb=HEAD
All this aside, as long as DO blocks don’t allow their contained
statements to be parameterized, you anyway have to use a procedure to
get the functionality that you need. This makes my question largely
moot—as Tom implied. So I’ll simply hope that, one day, the PostgreSQL
guardians will concede that implementing this missing DO functionality
would be useful—just as the Oracle Database guardians decided three
decades ago—and bring that functionality in a future PG release.
My experience is when I get to the point of needing parameters I'm
pretty much going to need the other plpgsql features. I could see having
it, but I do not remember seeing any/many previous posts to this list
requesting it. That pushes it down to the bottom of the must haves.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
