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

I would be interested in hearing how we can implement Oracle packages in
a way that seamlessly integrates into what we have.  Is it like
functions that are automatically called when a schema is accessed?  And
the result put into a per-session temporary schema?

I think it is unlikely we would implement Oracle packages exactly like
Oracle but I think there is interest in adding that functionality to
PostgreSQL.

If we can work up a list I can add it to the TODO list.

---------------------------------------------------------------------------

Josh Berkus wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073