Re: schema support, was Package support for Postgres

On 19 Oct 2001, Gunnar [iso-8859-1] Rnning wrote:

> * Bill Studenmund <wrstuden(at)netbsd(dot)org> wrote:
> |
> | Packages aren't schemas. What they bring to the table is they facilitate
> | making stored procedures (functions). You can have twelve different
> | developers working on twenty different packages, with no fear of name
> | conflicts. The package names will have to be different, so there can be
> | functions with the same names in different pacakges.
>
> Hmm. But if we had schema support can't we just package those procedures
> into a schema with a given name ? Maybe my stored procedures needs some other
> resources as well that should not conflict with other packages, like temp
> tables or such. It then seems to me that using schemas can solve everything
> that packages do and more ?

Assuming that schema support covers functions (which Tom, I, evidently
you, and Oracle think it should but which isn't mentioned at least in
SQL92), you could do that. And if you're adding tables, you probably
should.

But a lot of times you don't need to go to the effort of namespacing off a
whole new schema, and I can think of some cool things to do when you
don't.

One example is a large, complicated db app with multiple programmers. For
each general area of the app, you can create a package. That way you
modularize the code into more managable pieces. But since the are all in
the same schema, they can maintain/interact with the same tables.

So that's an arguement for packages/subschemas.

> | For the most part, I think packages and schemas are orthogonal. I'm taking
> | a cue from Oracle here. Oracle considers packages to be a schema-specific
> | object.
>
> What is really the difference functionality wise of making a subschema and
> package ? In both cases you deal with the namespace issues.

A matter of what is subspaced. I'd assume that a subschema namespaces off
everything a schema does. A package however only namespaces off functions
and aggregates. Packages, at least as I've implimented them, do *not*
namespace off types nor operators they contain.

Technically, the package oid is a key in the name index for pg_proc and
pg_aggregate, while it is not for pg_type and pg_operator.

I admit, I took a minor liberty here. Oracle packages do have types, but
Oracle types are not as rich as PostgreSQL's So when I was translating
packages, I made the types in them match PostgreSQL's. Also, since I'd
added aggregates and types, adding operators seemed like a reasonable
thing. Both from the point of view of the parser (they are all done about
the same way), and from the point of utility. PostgreSQL's ability to add
types is really cool, and the ability to add operators makes new types
convenient to use. If packages could add types and support functions but
not operators, that'd seem lame.

The reason that packages don't namespace off types and operators is I
think it makes them more useful. Think about the complex number example in
the programmer's guide. I can think of scientific applications which could
use them. But having to say package.complex for the type would be
combersome. And even worse, having to say package.+ or package.- would be
bad. And package.* might be ambiguous to the parser!

So that's why I made pacakges not be subschemas. Packages were designed to
help with writing stored procedures, and to do it well. :-)

Take care,

Bill