On 12/13/21 13:15, Bryn Llewellyn wrote:
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
https://stackoverflow.com/questions/53040170/postgresql-inline-function-behavior
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@xxxxxxxxxxxxx wrote:
Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.)
regards, tom lane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
