Re: SQL-standard function body

st 1. 7. 2020 v 15:37 odesílatel Robert Haas <robertmhaas(at)gmail(dot)com> napsal:

> 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.
>

plpgsql_check can be integrated to upstream.

https://github.com/okbob/plpgsql_check

> 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.
>

The standard SQL/PSM is a full functionality language with variables,
conditional statements, exception handlings, ..

https://postgres.cz/wiki/SQL/PSM_Manual

Unfortunately a basic implementation integrated into the main SQL parser
can be pretty hard work. First issue can be SET statement implementation.

Regards

Pavel

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