Re: Packages: Again

>
> * A design that can fit in with PostgreSQL
> * Solid benefits beyond "makes life easier for Oracle users" to
> justify each feature/change
> * Funding/time to make it happen
>
> So far, I haven't seen anyone with one of those, let alone all three.
OK, I’ll bite…

* In SFDC’s extension of PostgreSQL we nest namespaces.
This was done before my time here, but its very stable. It's easy to keep merged and not that much code.
To make the special semantics of these nested namespaces evident however we leaned on the SQL/PSM standard and call them MODULE’s.
Unlike the standard our MODULEs share the namespace (no pun intended) with regular schemata which seems practical and limits confusion when referencing
a module without schema qualification.

We did extend upon the standard with ALTER MODULE .. ADD [FUNCTION | TYPE | …] syntax.
Just like few users create a new schema with tables in one statement, no-one actually creates a module with content in one statement (unless, as in Oracle they have to).
This was done before my time as well, but parallels what we implemented did in DB2 for the reasons described earlier in this thread.
You want to be able to modify members of a module separately.

Starting with a blank slate I do wonder whether simply allowing nesting of namespaces would be sufficient to achieve the vast majority of the goal.
I.e. CREATE SCHEMA <schema>.<newschema>
The rest… follows trivially :-)

* Benefits:
a) The files on my computer are organized in directories that have more than one level of nesting.
I simply can’t imagine having thousands or tens of thousands of objects lying around and only one coarse way of subdividing them.
This is compounded by the desire you version. I want to the same names for objects across multiple concurrently present versions of the schema.
If I consume the schema for the version the entire schema for a version becomes a flat jumple.
b) Access control
By putting things that belong together actually together in an explicit way I can achieve scoping without having to resort to permissions.
I can simply postulate that all objects in a module are private unless they are published.
Access control happens at the module level.
This is no different than library management on your OS.
You don’t chmod the individual entry points!
c) Scoping
Similar to the above, but more related to search path.
Within a module I can be assured that any unqualified references will first resolve within the module.
No mucking with the search path by anyone will cause me to execute the wrong function, resolve to the wrong type etc.

Simply put: As long as we agree that users want to implement substantial server side logic the conclusion that standard programming
abstractions such as classes and member functions are a boon seems to be obvious.

Note that I have been careful not to tie modules too strongly to specific types. Conceptually I see nothing from with a module, table, view, etc.
It’s just a bit more “far out” since there is AFAIK no precedence.

* IFF our existing efforts (fast defaults and executor runtime improvements) to work with the community are successful I would happily lobby
to at least port our module code to the community codebase. We can take it from there.

Cheers
Serge Rielau
Salesforce.com