Re: Why can't I have a "language sql" anonymous block? - Mailing list pgsql-general

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
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.)
 

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’tbe 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
guessthat 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
objectthat’s used 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
justreferred to: 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
thatit 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@aklaver.com