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