Re: Note about comparation PL/SQL packages and our schema/extensions

On Thu, Nov 5, 2015 at 9:36 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> I had talk about possibility to implement PL/SQL packages in Postgres.
>
> The package concept is coming from ADA language and it is partially
> foreign/redundant element in SQL world. Oracle needs it for modularization,
> because schema plays different role there than in Postgres. My opinion
> about packages in Postgres is clean - the concept of schemas and extension
> is simple and just work. I don't see any big gap there. If we don't play
> Oracle compatibility game, then we don't need to implement class like
> Oracle package. But there are few features, that can help to PL/pgSQL
> developers - generally or with porting from Oracle.
>
> 1. The encapsulation and local scope - all objects in schema are
> accessible from other objects in schema by default (can be rewritten by
> explicit granting). Local objects are visible only from objects in schema.
> This needs enhancing of our search_path mechanism.
>
> 2. The schema variables - a server side session (can be emulated now) and
> server side local schema session variables (doesn't exist) is pretty useful
> for storing some temp data or high frequent change data - and can
> significantly increase speed of some use cases. Now we emulate it via
> PLPerl shared array, but the encapsulation is missing.
>
> 3. The initialization routines - the routines called when any object from
> schema is used first time.
>
> All three features we can emulate relative simply in C, and probably for
> all mentioned points we have some workaround (less/more ugly) for PL/pgSQL.
> Can be nice do it cleanly in PLpgSQL too.
>

I'd say go ahead ! Packages support is the one of the most requested
feature of people migrating from Oracle.

>
> I don't think we need ADA/ | PL/SQL Syntax - we can enhance our extension
> mechanism to support mentioned points.
>
> Comments, notes?
>
> Regards
>
> Pavel
>