Re: Why can't I have a "language sql" anonymous block?

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> adrian(dot)klaver(at)aklaver(dot)com wrote:
>>
>>> Bryn wrote:
>>>
>>> I used a procedure to test this because functions shouldn’t do DDL. I started with a working “language plpgsql” example…
>>
>> Since procedures are relatively new to Postgres you are going to find more functions doing DDL then procedures. Not sure I follow why one is preferred over the other anyway?
>
> (1) Unless you are doing transaction control our implementation doesn’t really give a preference. But from a theory perspective functions are ideally side-effect free while procedures are not. DDL, and even DML, cause side-effects and so are better done within a procedure. Having side-effects in a SELECT query is likewise not desirable so the inability to actually execute a procedure in the middle of a SELECT command doesn't pose a conceptual problem.
>
> (2) As for the main question of allowing anonymous procedures to be written in SQL, I too don't see much benefit. The pl/pgsql implementation is basically a superset, aside from adding BEGIN/END; you can simply pretend you are writing plain SQL in the DO body and it should work. Now, would we reject a well-written patch that made it work? Probably not. But given the fact that DO is not a standard proscribed feature, and pl/pgsql works, I see little motivation for anyone to simply complete the symmetry. If anything, the fact that these procedures would mostly be used for "side-effect causing actions" means that added overhead of the language tends to 0% of the overall execution time as the procedures become more complex and thus benefit more from being wrapped.

Thanks, David.

Re your paragraph #1, yes: that’s similar to how I’d’ve answered. I might’ve said, too, that a function is invoked as a term in an expression—and expression evaluation is meant to be side-effect free. Following on, just like how variables are named, a function name should be a noun (phrase). Having said this, the planet has an uncountable number of “library” status functions whose names are imperative verb phrases. Many start with “get”. So this orthography battle is well and truly lost. On the other hand, folks find it fairy natural to name procedures with imperative verb phrases.

Having said all this, the SQL language rather muddies the waters with its “returning” clause in a change-making statement. I s’pose that the purist would call “update… returning” a procedure with an out parameter rather than a function with a side effect. But I can be as pragmatic as the next programmer, stop fussing, and write a (volatile) function with a side effect when I think that it’s be nice.

Re your paragraph #2, I already made the case for anonymous procedures. And I said that, to deserve the name, they must allow parameterization. They bring their value in a certain kind of scripting where you want to do stuff but leave no secondary traces. Plus the point about whether you even have the privilege to create objects. However, nobody here was convinced by this thinking.

I do think that it’s risky to dismiss as valueless some feature that, for example, Oracle Database has (and has had since the dawn of time), and that PG lacks, unless the feature is intertwined with specific aspects of the other environment that have no counterpart in PG. The extreme example of this thinking is to dismiss the notion of PL/pgSQL packages and inner procedures as valueless except in that they might ease migrations from Oracle Database to PG.