Re: [HACKERS] Packages: Again - Mailing list pgsql-hackers
2017-02-03 18:30 GMT+01:00 Serge Rielau <serge@rielau.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:
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
CheersSerge
pgsql-hackers by date: