> > Still I little bit afraid about nesting - Postgres allows function overloading with specific mechanism of selecting called function. Sometimes it is problematic now, and the this structure is flat. > > I like a idea of more close relation between function and schema. This means implicit setting of SEARCH_PATH to function schema. It is simply but powerful idea. > > CREATE FUNCTION mod.func() > AS $$ $$ MODULE VISIBILITY > > can be dynamically executed like > CREATE FUNCTION mod.fun() > AS $$ > SET SEARCH_PATH TO "mod,$SEARCH_PATH"; > .. > $$; Ah, yes. It is my understanding that PG treats functions more like macros.
Only SQL functions can be used like macros - It is not possible with PLpgSQL
That is the search_path for queries inside the function is not fixed to the one in effect when the function was defined. This does have advantages in some cases, but it hurts in this case.
yes. This "functions pined to schema" should not be necessary implemented with injection to SEARCH_PATH. We can introduce some internal flag, so the explicit change of SEARCH_PATH doesn't break it. But this behave should be allowed for specially marked functions. It can ensure so functions from same schema is preferred without any setting of SEARCH_PATH. What can have a security benefit.
What you are describing is syntax to force that in some form or other. This is actually not hard to do at all. PG already records the search path in the function cache (I think) and plan source cache (I know), just not in pg_proc. If PG supported this functionality it would improve the mapping. > I understand well so missing nested structures is big complication when you do port from environment where this functionality is used. But the nesting means usually more complex solution - and in these days I don't believe so it is necessary. PLpgSQL doesn't allow nested functions - it is strongly reduced against original PL/SQL - and it is visible only when you do migration from Oracle. I’m not talking about nested PLpgSQL function definition. That is indeed rather advanced and while I have seen it (and its’s supported in DB2 of that reason) I would not consider it high priority. Multiple packages in different schema are common however because applications use schemas for versioning. That’s why flattening the package into a schema as supported today does not work in these cases.