Re: Oracle Style packages on postgres

Rmm,

> "A black box processing engine with one or more public access functions
> that retains state across calls"

In other words, an Object. <grin>

> Oracle style package creation syntax is split into header and body so that
> the body(code) can be re-compiled without invalidating dependent objects.
> Postgres syntax for the dbms_output example (in any postgres server side
> language) would be along the lines of:
> CREATE OR REPLACE PACKAGE HEADER dbms_output AS
> FUNCTION dbms_output_put_line(text) RETURNS text,
> FUNCTION dbms_output_get_lines() RETURNS text;
> CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
> <language>;

Hmmm. What about package variables? For me, this is one of the most
valuable parts of packages.

I've also never much liked Oracle's seperate package_header and package_body
declaration structure: if the two are intrinsically tied, why not make it one
declaration? Is syntactical compatibility important enough that we need to
imitate their design errors?

> Adding pg_package with a link from pg_proc are the only changes required
> to the data dictionary.
> It would be nice to have similar dotted syntax as oracle
> (user.package.function) but would this mess up postgres namespaces?

Yes, actually. If you look at the discussion, this is what killed the 2001
proposal; packages were proposed as orthagonal to schema which was not
acceptable.

However, now that schema are well established, it seems like this namespace
issue is limited. The problem would be that you'd have to make sure that no
two schema and packages had the same name, or that there would be an
automatic precedence of shema, package established.

So, given a shema named "dataloader" and a package named "dataloader" and a
function named "copy_it(filename)", what would happen is:

dataloader.dataloader.copy_it('/tmp/somefile')
... would be absolutely clear
dataloader.copy_it('/tmp/somefile')
... would attempt to call the copy_it function in the dataloader
*schema*, not the dataloader *package*.

The above seems inevitable, and not really a problem to me. We simply warn
people in the docs of the behavior, and to avoid duplicate naming.

I think there are more important questions:

1) how do you prevent users from executing the package functions outside of
the package?
2) Have you taken care of package variables? If so, are they only
per-session, or global? If they are global, how do you accomplish this?
3) For that matter, is initialization per session or global?

--
Josh Berkus
Aglio Database Solutions
San Francisco