Re: Oracle Style packages on postgres - Mailing list pgsql-hackers

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