I'm following up from Jim's POC for adding MODULE to PostgreSQL. [1]
My proposal implements modules as schema objects to be stored in a new system catalog pg_module with new syntax for CREATE [OR REPLACE] MODULE, ALTER MODULE, DROP MODULE and for GRANT and REVOKE for privileges on modules and module routines. I am attempting to follow the SQL spec. However, for right now, I'm proposing to support only routines as module contents, with local temporary tables and path specifications as defined in the SQL spec, to be supported in a future submission. We could also include support for variables depending on its status. [2]
Following are some examples of what the new module syntax would look like. The attached patch has detailed documentation.
CREATE MODULE mtest1 CREATE FUNCTION m1testa() RETURNS text LANGUAGE sql RETURN '1x'; SELECT mtest1.m1testa(); ALTER MODULE mtest1 CREATE FUNCTION m1testd() RETURNS text LANGUAGE sql RETURN 'm1testd'; SELECT mtest1.m1testd(); ALTER MODULE mtest1 RENAME TO mtest1renamed; SELECT mtest1renamed.m1testd(); REVOKE ON MODULE mtest1 REFERENCES ON FUNCTION m1testa() FROM public; GRANT ON MODULE mtest1 REFERENCES ON FUNCTION m1testa() TO regress_priv_user1;
I am new to the PostgreSQL community and would really appreciate your input and feedback.