st 2. 6. 2021 v 15:39 odesílatel Jim Mlodgenski <jimmy76@gmail.com> napsal:
Working with users over the years, some have large libraries of server side code sometimes consisting of 100k+ lines of code over 1000+ functions and procedures. This usually comes from a migration of a commercial database like Oracle where it was best practice to put all of your business logic into stored procedures. In these types of apps, just managing the code is a challenge. To help classify objects, schemas are used, but you are at the mercy of a naming convention to show association. For example, a frequent naming convention would be having related schemas with the names of foo_bar and foo_baz. For devs, that's akin to keeping a file like xlog.c in a directory structure like backend_access_transam instead of backend/access/transam. IMHO, having a hierarchy makes it simpler to reason about related code bits.
The SQL spec does have a concept of modules that help address this. It's defined as a persistent object within a schema that contains one or more routines. It also defines other things like local temporary tables and path specifications. There are other databases like DB2 that have implemented module support each with their own way of defining the routines within the module. The spec doesn't really give guidance on how to manipulate the objects within the module.
Attached is a POC patch for modules. I modeled it as a sub-schema because that is more what it seems like to me. It adds additional columns to pg_namespace and allows for 3-part (or 4 with the database name) naming of objects within the module. This simple example works with the patch.
CREATE SCHEMA foo; CREATE MODULE foo.bar CREATE FUNCTION hello() RETURNS text LANGUAGE sql RETURN 'hello' CREATE FUNCTION world() RETURNS text LANGUAGE sql RETURN 'world'; SELECT foo.bar.hello();
Questions - Do we want to add module support?
- If we do, should it be implemented as a type of namespace or should it be its own object type that lives in something like pg_module?
- How should users interact with objects within a module? They could be mostly independent like the current POC or we can introduce a path like ALTER MODULE foo ADD FUNCTION blah
I never liked the SQL/PSM concept of modules. The possibility to assign database objects to schema or to modules looks like schizophrenia.
There are only two advantages of modules - a) possibility to define private objects, b) local scope - the objects from modules shadows external objects without dependency of search_path.
But both these features are pretty hard to implement in PL/pgSQL - where expression executor is SQL executor.
Without these features I don't see strong benefits for modules.
