Re: Why can't I have a "language sql" anonymous block?

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@aklaver.com