Re: Packages: Again

2017-02-03 23:24 GMT+01:00 Serge Rielau <serge(at)rielau(dot)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