Re: Packages: Again

2017-02-03 18:30 GMT+01:00 Serge Rielau <serge(at)rielau(dot)com>:

> > DB2 propose using schemas instead packages
>> >
>> > https://www.ibm.com/developerworks/data/library/
>> techarticle/dm-0711zubiri/
>
> That article by Adriana is 6 years ago and was written actually while we
> implemented MODULE’s for DB2 9.7. So yes, when you don’t have modules,
> schemata are the way to go in the same way as when all you have is a hammer
> everything is a nail.
> We considered MODULEs an absolute must to get functional equivalency to
> Oracle PL/SQL packages. Also they wouldn’t take up so much space in the
> standard if they would be deemed to provide no function...
>

The DB2 Modules is not exactly ANSI SQL modules (but it's maybe better -
the ANSI concept is maybe obsolete) - and if I remember the SQL/PSM the
modules related part is few percent only.

> > Now I am working with Oracle application - and I try to understand to
>> Oracle
>> > developers - often pattern is using "Oracle schema" as database - and
>> then
>> > the packages has sense. But there is not a mapping "Oracle schema" =
>> > "PostgreSQL schema" - and packages is a redundant concept in Postgres
>> (and
>> > in all db, where the schema are like namaspace - MSSQL, DB2, MySQL).
>
> I have never heard the claim that database in Oracle matches schema in
> other DBMS.
> In my experience Oracle is well in line on the schema front with the
> exception of the one-to-one relationship between schema and user.
>
> The database-is-really-a-schema mapping is something we (at DB2)
> traditionally associated with Sybase and SQL Server migrations where we saw
> plenty of small databases with cross database queries.
>
> Having said all that I think schemata are quite powerful in Postgres, not
> least because of the clean usage of search_path for all object resolution
> and schema being independent of user. They get us a fair ways.
> The main gap remains the inability to do any sort of nesting.
> To have two “package-like-things” with the same name.
>

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";
..
$$;

It is simple when schema are not nested. But when we allow nested, then
implementation will be significantly harder - is possible to see inside
nested schema, or see to outer schema, or visibility is flat, and nested
objects should be qualified every time?

>
> I’m not going to repeat myself on that one and bore everyone.
> My thinking on modules is someone reflected here:
> https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/
> module?lang=en
>
>
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.

My fresh experience from porting some old school Oracle application to
Postgres. Now, probably the biggest problem is small detail - empty string
is NULL in Oracle.

Regards

Pavel

> Cheers
> Serge
>