Re: proposal for PL packages for 8.3. - Mailing list pgsql-hackers

On Wed, Aug 09, 2006 at 11:48:41AM +0200, Pavel Stehule wrote:
> 
> >There are three separate issues we seem to be talking about.
> >
> >1. Namespaces - visibility or otherwise of objects
> >2. Procedural state - something that looks like a shared variable
> >3. Packaging - installation/dependency handling
> >
> and 4. support more languages:
> 4a) binary incompatibility between variables different PL
> 4b) two types for calling functions [native and SPI]
I see a lot less use for more languages, since other languages already
have most of the features of a package.

> >Namespaces
> >Given that we already have search_path it makes sense to use it. So, we 
> >could have something like:
> >1. A "PRIVATE" modifier for objects that mean they are only accessible if 
> >their schema is the first in the search_path.
> >2. A definable search_path for a schema, so all objects have that setting 
> >by default. In theory, this could break dynamic queries in functions that 
> >relied on a changeable search_path.

ISTM that messing with search_path is a lot more obtuse than simply
labeling something as PRIVATE or PUBLIC when you define it.

Oracle's handling of packages is actually quite elegant; it's worth
looking at for anyone who's not familiar with it. In a nutshell, it
looks something like this:

CREATE OR REPLACE PACKAGE foo (

declare global variables;
-- Variables can be public or private, but the accepted practice is to
-- make them all private and define accessor functions for them

BEGIN;   -- block of code to be executed on first invocation
END;

CREATE FUNCTION public_function() ...

CREATE PRIVATE FUNCTION bar();
);

This is actually split into two parts, a package header and a package
body. The header defines all the external "API".. what functions are
public, their parameters, etc. The body contains the actual code. My
guess is this was done so that you can change the body at will without
invalidating all the plan caches in the entire database.

> >Procedural state
> >Just a convenient way of defining some small amount of state. Will need 
> >session variables and static shared variables. Presumably we'll need to be 
> >able to lock shared variables.
> >
> >Packaging
> >I'd guess we'd need a pg_package and pg_package_items system tables. We 
> >could track:
> >- package name (different from schema)
> >- version number
> >- install/uninstall functions
> >- start-session/end-session functions
> >- dependencies (is pg_depend enough)
> >pg_package_items
> >- schema-name
> >- variables, functions, tables, views etc
> >
> it's strong but little bit complicated system.  Start session and end 
> session is better to solve via session's triggers. Install, uninstall, +/- 
> I can understand sence, but I can call it manually. I need loader of 
> package which is evaluated when somebody first call any function from 
> package. This loader can initialize and create package variables (Perl 
> don't has variable's declaration).  Can somebody say what is ANSI SQL? I 
> haven't text of it :-(. I found only basic syntax of "CREATE MODULE".

Oracle has no concept of a 'session' functions. It only allows you to
run a block of code the first time a package is called in a session, so
that you can do setup.

I'm not really sure what you'd use install/uninstall functions for.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461