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.)
t...@sss.pgh.pa.us wrote:
Bryn Llewellyn <b...@yugabyte.com> 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.kla...@aklaver.com
