Re: Why can't I have a "language sql" anonymous block? - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Why can't I have a "language sql" anonymous block? |
| Date | |
| Msg-id | 05E2EA9E-B353-4D69-B255-3BAAA5954936@yugabyte.com Whole thread Raw |
| In response to | Re: Why can't I have a "language sql" anonymous block? (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Why can't I have a "language sql" anonymous block?
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
Adrian Klaver 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.
Bryn wrote:
As in?:
begin;
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
commit;
The 'Law of Minimums', use the minimum needed functionality to get the job done. Less chance of wandering into areas where there be dragons.
Also inlining:
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
Well… that’s the answer: “nobody thought it’d be useful”. Thanks, Tom.
The difference between using a “language sql” anonymous block and just executing the contained SQL statements? is partly a clear declaration of the intent of your code and a guarantee that all the statements are sent from client to server in one go. (But see what I say about “prepare” below.)
As in?:
begin;
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
commit;
Here’s how I’d reason the case. There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former wouldn’t be supported.
The 'Law of Minimums', use the minimum needed functionality to get the job done. Less chance of wandering into areas where there be dragons.
A “language sql” procedure has restricted functionality compared with a “language plpgsql” procedure. So I can only guess that it’s preferred when it lets you program what you need ‘cos simpler means quicker.
Also inlining:
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
In general, an anonymous block is preferred over a stored unit when you don’t want to clutter the schema with an object that’s used only occasionally. (In some cases, you don’t even have the privileges to create a stored unit but can execute an anonymous block. So putting these two ideas together makes the case for a “language sql” anonymous block.
Another reason to support “language sql” anonymous blocks is to improve symmetry—and therefore usability: one fewer arbitrary rule to remember.
B.t.w., you mentioned the restriction that DO blocks can’t have parameters. The reason for allowing them is what I just referred to: don’t want to, or simply cannot, create a procedure or function. Oracle Database allows binding to placeholders in anonymous blocks (their vocabulary for “ parameters”)—and, as I recall, has since their very first appearance as a feature.
Might your “they don't so far” give me hope that they presently will be? Presumably, the notion would have to include the ability to prepare-once and execute-many using an anonymous block. (This is another counter intuitive restriction that, today, has to be learned.)
tgl@sss.pgh.pa.us wrote:
Bryn writes:Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought that it would be useful?I think nobody thought it'd be useful. What's the difference from just executing the contained SQL statements?
(If DO blocks had parameters, the conclusion might be different, but they don't so far.)
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.
About the four explicit top-level SQL statements:
begin;
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
commit;
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
commit;
versus one DO block SQL statement:
do $body$
begin
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
end;
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
end;
$body$;
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.
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.
pgsql-general by date: