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

On Wed, Dec 15, 2021 at 2:37 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/15/21 13:05, Bryn Llewellyn wrote:
> >> mmoncure(at)gmail(dot)com <mailto:mmoncure(at)gmail(dot)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.