Hi st 2. 6. 2021 v 15:39 odesÃlatel Jim Mlodgenski <jimm...@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. Regards Pavel > > --Jim >
