Re: SQL-standard function body

On Tue, Jun 30, 2020 at 2:51 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> On further thought, we probably don't have to. Re-parsing the function
> body the same way is exactly the same problem as re-parsing a view or
> matview body the same way. I don't want to claim that that's a 100%
> solved problem, but I've heard few complaints in that area lately.
>
> The point remains that exposing the function body's dependencies will
> constrain restore order far more than we are accustomed to see. It
> might be possible to build examples that flat out can't be restored,
> even granting that we teach pg_dump how to break dependency loops
> by first creating the function with empty body and later redefining
> it with the real body. (Admittedly, if that's possible then you
> likely could make it happen with views too. But somehow it seems
> more likely that people would create spaghetti dependencies for
> functions than views.)

In my experience, there's certainly demand for some kind of mode where
plpgsql functions get checked at function definition time, rather than
at execution time. The model we have is advantageous not only because
it simplifies dump and reload, but also because it handles cases where
the table is created on the fly properly. However, it also means that
you can have silly mistakes in your function definitions that you
don't find out about until runtime, and in my experience, people don't
like that behavior much at all. So I don't think that it's a bad idea
on principle, or anything like that, but the details seem like they
need a lot of thought. The dump and restore issues need to be
considered, but also, what about things like IF and WHILE? People are
going to want those constructs with these new semantics, too.

I actually don't have a very clear idea of what the standard has to
say about SQL-language functions. Does it just say it's a list of
statements, or does it involve variables and control-flow constructs
and stuff like that, too? If we go that direction with this, then
we're actually going to end up with two different implementations of
what's now plpgsql, or something. But if we don't, then I'm not sure
how far this takes us. I'm not saying it's bad, but the comment "I
love the early binding but where's my IF statement" seems like an
inevitable one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company