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
partlya clear declaration of the intent of your code and a guarantee that all the statements are sent from client to
serverin one go. (But see what I say about “prepare” below.)
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’tbe supported.
A “language sql” procedure has restricted functionality compared with a “language plpgsql” procedure. So I can only
guessthat it’s preferred when it lets you program what you need ‘cos simpler means quicker.
In general, an anonymous block is preferred over a stored unit when you don’t want to clutter the schema with an object
that’sused only occasionally. (In some cases, you don’t even have the privileges to create a stored unit but can
executean 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
arbitraryrule 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
referredto: don’t want to, or simply cannot, create a procedure or function. Oracle Database allows binding to
placeholdersin anonymous blocks (their vocabulary for “ parameters”)—and, as I recall, has since their very first
appearanceas a feature.
Might your “they don't so far” give me hope that they presently will be? Presumably, the notion would have to include
theability 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 Llewellyn <bryn@yugabyte.com> writes:
> Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought that
itwould 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
