On Wed, Dec 15, 2021 at 2:37 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 12/15/21 13:05, Bryn Llewellyn wrote:
>> mmoncure@xxxxxxxxx <mailto:mmoncure@xxxxxxxxx> wrote:
> — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
> About your point #1…
>
> 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?
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.
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.
David J.
