2017-02-03 23:24 GMT+01:00 Serge Rielau <se...@rielau.com>: > > > > > 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. > I used nested functions just like example. Any recursive structure can be flatted/unfolded. > > Cheers > Serge
