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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

laurenz.albe@xxxxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

The advantages are self-evident to these programmers…

I am not trying to belittle this, but when you are used to system A and start working with system B you always miss some features of A, until you get to know B better and figure out how to do things there.

Several people have responded with various points—some technical and some political. Thanks to all of you. I’m simply using the most recent turn in the thread as the hook for my present turn.

I made a terrible essay design choice with my “advantages of packages are self-evident”. I used this as a wrong-headed shortcut to save myself the effort of writing about modular software design principles—and to save you all the effort of (re)reading that stuff. So I’ll make the briefest attempt here.

«

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.

Inner subprograms are a very nice bonus because they allow locally useful helpers to be implemented right where they’re needed in one subprogram so that they’re invisible to all other subprograms in the body. The scope rules here a simply an extension of what PG already supports with block statements inside block statements.

There’s also the business of globals, at any level, and package-level globals that bring package state. Some programmers have religious objections here. But the value of constants declared in the package spec seems to be seen by all who program using packages as only useful.

»

I firmly believe that the intrinsic value of all of this has nothing to do with Oracle Database, with migrating from it to PG, or with Ada.  It’s just that Oracle’s PL/SQL has a working implementation. And many people find it easier to think when they can experiment with something concrete rather than trying to hold, and run, a pretty big abstract model entirely in their head.

Anyway… enough of this. I fear that even what I said above will annoy some folks on this list.

It seems to me that there’s sufficiently vigorous opposition to anything like packages for PL/pgSQL that such an enhancement will never happen. So I must just accept this and (as Laurenz recommends) learn the best design patterns for singleton PL/pgSQL functions and procedures in PG as it presently is.

When I first started to use PG, I read “43.13. Porting from Oracle PL/SQL” (www.postgresql.org/docs/current/plpgsql-porting.html). These are the relevant bullets: «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.»

This says nothing about how to model the spec/body distinction. I experimented with various schemes. For example, one schema for the exposed API and another one for the to-be-hidden implementation. This depends on a careful, practice-based, use of the privileges scheme and implies using “security definer” units. But you can’t do transaction control in such a unit—and this brings its own problems. I tried to model package state using temporary tables but I hit what seemed to be a bootstrap conundrum. How, in the absence of a trigger that fires when a session starts, can I make sure that the table that I want is in place?

Can anybody please recommend a whitepaper, or similar, that explains the recommended practice in this space?