Re: Oracle Style packages on postgres

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(at)postgresql(dot)org)
>

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