On Wed, Dec 15, 2021 at 2:37 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 12/15/21 13:05, Bryn Llewellyn wrote: > >> mmonc...@gmail.com <mailto:mmonc...@gmail.com> 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.
