Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

bryn(at)yugabyte(dot)com wrote:
>
>> pg(at)bowt(dot)ie <mailto:pg(at)bowt(dot)ie> wrote:
>>
>>> bryn(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com> wrote:
>>>
>>> Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.
>>
>> I still don’t get it. It sounds like you’re mostly talking about encapsulation, or Information hiding, for stored procedures. I can certainly see how plpgsql doesn’t do those things very well, but it still seems like there might be a lot of nuance that isn’t getting across. The list of specific features that seem to be missing are not unreasonable, individually, and yet it feels like I cannot see some bigger picture that's apparent to you.
>>
>> Maybe you should explain your position by way of a motivating example, involving a real world use case. Something that makes the issues concrete. Are these items compelling because of how they allow an organization to deploy a program in a production environment, complete with version control? Does it have something to do with decoupling the mutable business data stored in tables from the programs contained/run in the same database?
>
> I can certainly make up an example. I’ll do this over the weekend. However, I fear that it will be time wasted because at least some of the addressees here who’ve expressed strong opposition to the notion of PL/pgSQL packages must understand very well what they’re objecting to. For example, pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com> with his “schema variables, LET command” work.
>
> Anyway… I’ll give it my best shot. I’ll try to address your specific questions in my follow-up reply. Hang on for a couple of days, please.

I made a start on this. But I want to think carefully about the example use case(s). So I won’t promise a delivery date. Like I said, I don’t expect to change anybody’s mind. But I do hope that I might get some useful suggestions on how, using PG Version 14, I can best meet the requirements that I’ll aim to explain.

I’m still hoping that I might get some pointers to whitepapers or blog posts that expand on those bullets that I quoted from the PG doc: «Instead of packages, use schemas to organize your functions into groups.» and «Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.»