Fwd: [HACKERS] Package support for Postgres - Mailing list pgadmin-hackers

 From Hackers.

>Delivered-To: jm.poure@freesurf.fr
>Date: Thu, 11 Oct 2001 13:12:32 -0700 (PDT)
>From: Bill Studenmund <wrstuden@netbsd.org>
>X-X-Sender:  <wrstuden@vespasia.home-net.internetconnect.net>
>To: <pgsql-hackers@postgresql.org>
>Subject: [HACKERS] Package support for Postgres
>Sender: pgsql-hackers-owner@postgresql.org
>
>Zembu has decided to release the result of a recent Postgres developement
>project to the Postgres project. This project (for which I was the lead
>developer) adds Oracle-like package support to Postgres. I'm in the
>process of making a version of the patch which is relative to the current
>cvs tree. The change is fairly encompasing, weighing in at around
>800k of unified diffs, of which about 200k are the real meat. Before I
>send it in, though, I thought I'd see what people think of the idea. Oh,
>this feature would definitly be a 7.3 feature, 7.2 is too close to the
>door for this to go in. :-)
>
>This message is rather long. I've divided it into sections which start
>with "**".
>
>** What are Packages
>
>So what are packages? In Oracle, they are a feature which helps developers
>make stored procedures and functions. They provide a name space for
>functions local to the package, session-specific package variables, and
>initialization routines which are run before any other routine in the
>package. Also, all parts of a package are loaded and unloaded at once -
>you can't have it partially installed.
>
>All of these features make life much easier for stored-procedure
>developers. The name space feature means that you can name the routines in
>your package whatever you want, and they won't conflict with the names
>either in other packages or with functions not in a package. All you need
>to do is ensure that no other package has the same name as yours.
>
>** What did I do, and what does a package declaration look like?
>
>What I've done is impliment Oracle packages with a Postgres flair. There
>is a new command, CREATE PACKAGE <name> AS which defines a package. For
>those of you up on Oracle packages, this command duplicates the Oracle
>CREATE PACKAGE BODY command - there is no Postgres equivalent of the
>Oracle CREATE PACKAGE command.
>
>Packages are listed in a new system table, pg_package, and are referenced
>in other tables by the oid of the row in pg_package.
>
>There are seven different components which can be present in a package,
>and so a CREATE PACKAGE command contains seven stanza types. A package can
>be made up of functions, types, operators, aggregates, package-global
>variables, initialization routines, and functions usable for type
>declarations. Four of the stanzas are easy to understand;  to create a
>function, a type, an aggregate, or an operator, you include a stanza which
>is the relevant CREATE command without the CREATE keyword. Thus the
>FUNCTION stanza creates a function, the TYPE stanza creates a type,
>AGGREGATE => an aggregate, and OPERATOR => an operator.
>
>The initializer routines and package-global variables are done a bit
>differently than in Oracle, reflecting Postgres's strength at adding
>languages. Postgres supports six procedural languages (plpgsql, pltcl,
>pltclu, plperl, plperlu, and plpython) whereas I think Oracle only
>supports two (PL/SQL and I herad they added a java PL). The main
>difference is that the variables and the initializer routines are language
>specific. So you can have different variables for plpgsql than for pltcl.
>Likewise for initializers.
>
>Package-global variables are defined as:
>DECLARE <variable name> '<variable type>' [, <next name> '<next type>' ]
>LANGUAGE 'langname'
>
>The type is delimited by single quotes so that the postgres parser didn't
>have to learn the syntax of each procedural language's variable types.
>
>Initializer routines are declared like normal functions, except the
>function name and signature (number & type of arguements and return type)
>are not given. The name is automatically generated (it is __packinit_
>followed by the language name) and the function signature should not be
>depended on. It is to take no parameters and return an int4 for now, but
>that should probably change whenever PG supports true procedures.
>Initializer routines are declared as:
>BODY AS 'function body' LANGUAGE 'lanname' [with <with options>]
>
>I'm attaching a sample showing a package initialization routine and global
>variable declaration. There's a syntax error in it, which I asked about in
>another EMail.
>
>The last component of a package are the functions usable for type
>declarations. They are declared as:
>BEFORE TYPE FUNCTION <standard package function declaration>
>
>They are useful as the normal functions in a package are declared after
>the types are declared, so that they can use a type newly-defined in a
>package. Which is fine, except that to define a type, you have to give an
>input and an output function. BEFORE TYPE FUNCTIONs are used to define
>those functions. Other than exactly when they are created in package
>loading, they are just like other functions in the package.
>
>I'm attaching an example which defines the type 'myint4' (using the
>internal int4 routines) and proceeds to declare routines using the new
>type.
>
>** So how do I use things in a package?
>
>You don't have to do anything special to use a type or an operator defined
>in a package - you just use it. Getting technical, operators and types in
>packages are in the same name space as are types and operators not in
>packages. To follow along with the example I attached above, the 'myint4'
>type is usable in the typetest package, in tables, in other packages, and
>in "normal" functions.
>
>For functions and aggregates, things are a little more complicated. First
>off, there is a package called "standard" which contains all types,
>aggregates, operators, and functions which aren't in a specific package.
>This includes all of the standard Postgres routines, and anything created
>with CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and CREATE TYPE.
>
>Secondly, parsing is always done in terms of a specified package context.
>If we are parsing an equation in a routine inside of a package, then the
>package context is that package. If we are just typing along in psql, then
>the package context is "standard".
>
>When you specify a function or aggregate, you have two choices. One is to
>specify a package, and a function in that package, like
>"nametest.process" to specify the "process" function in the "nametest"
>package.
>
>The other choice is to just give the function's name. The first place
>Postgres will look is in the package context used for parsing. If it's not
>there (and that context wasn't "standard"), then it will look in
>"standard". So for example in the type declaration example attached, the
>type stanza uses "myint4in" and "myint4out" as the input and output
>routines, and finds the ones declared as part of the package.
>
>I've attached a sample showing off namespaces. It has two non-package
>routines, and one package named "nametest".
>
>Here's a sample session:
>
>testing=# select standard.process(4);
>      process
>------------------
>  I am in standard
>(1 row)
>testing=# select nametest.process(4);
>        process
>---------------------
>  I am in the package
>(1 row)
>testing=# select nametest.docheck();
>        docheck
>---------------------
>  I am in the package
>(1 row)
>
>First we see that the standard.process() routine says it is in the
>"standard" package, and that the nametest.process() routine says it is in
>the package. Then we call the nametest.docheck() routine.
>
>It evaluates "process(changer(4));" in the context of the nametest
>package. We find the process() routine in the package, and use it.
>
>The changer routine is there to test how typecasting works. It verifies
>that Postgres would typecast the return of changer into a different
>integer and call the process() routine in the package rather than call the
>process() routine in standard. This behavior matches Oracle's.
>
>The other routines in the package show of some examples of how sql will
>parse according to the above rules.
>
>Initialization routines:
>
>There is only one recomended way to use them: call a function written in
>the same PL in the package. That will cause the initialization routine to
>be run. Assuming there are no errors, the routine you call won't be
>executed until after the initialization routine finishes.
>
>Of course the non-recomended way is to manually call __packinit_<langname>
>directly. The problem with that is that you are depending on
>implimentation details which might change. Like exactly how the name is
>generated (which probably won't change) and the calling convention (which
>hopefully will if procedures are ever suported).
>
>Package-global variables:
>
>Just use them. Assuming that the procedural language supports global
>variables, they just work. Note that as with Oracle, each backend will get
>its own set of variables. No effort is made to coordinate values across
>backends. But chances are you don't want to do that, and if you did, just
>make a table. :-)
>
>** So what is the state of the diffs?
>
>The diffs contain changes to last week's current (I'll cvs update before
>sending out) which add package support to the backend, plpgsql, the SPI
>interface, initdb, and pg_dump. The changes also include modifying the
>system schema to support packages (pg_package which lists packages,
>pg_packglobal which list global variables, and adding a package identifier
>to pg_aggretage, pg_operator, pg_proc and pg_type).
>
>The big things missing are documentation, and regression tests which
>explicitly test packages.
>
>Also, plpgsql is the only PL with package support. Adding package support
>doesn't make sense for the 'C' and 'internal' languages, as you can
>manually add "global" variables and initialization routines yourself. It
>also doesn't make sense for 'sql' as sql doesn't support variables. The
>other languages need to gain package support, and I'll appreciate help
>from their authors. :-)
>
>So I'd better wrap up here. Monday I'll send the diffs to the patches
>list, and also send a message talking about more of the details of the
>changes.
>
>What do folks think?
>
>Take care,
>
>Bill
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster