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

Bob wrote:
> One simple benefit to packages is just organization of related code.

And the package-scoped variables or constant values, similar to
the global variables.

It will be very useful for application programmers
if one variable can be shared from several functions.

I needed some tricks when I tried to port such PL/SQL to PL/pgSQL.

Bob wrote:
> One simple benefit to packages is just organization of related code.
> 
> On 5/7/05, *rmm@sqlisor.com <mailto:rmm@sqlisor.com>* < rmm@sqlisor.com 
> <mailto:rmm@sqlisor.com>> wrote:
> 
>     Oracle Style packages on postgres
> 
>     OVERVIEW:
> 
>     To emulate oracle server side development in postgres I required server
>     side packages.  The following text demonstrates how to do this using
>     plpython on postgres 8 and suggests a language extension.
> 
>     WHAT ARE ORACLE PACKAGES?
> 
>     Looking back over the postgres discussion forums (particulary a
>     discussion
>     in 2001 following a proposal by Bill Studenmund) there appears to be
>     some
>     confusion over what oracle packages are.  Here's a concise definition :
>        "A black box processing engine with one or more public access
>     functions
>     that retains state across calls"
>     An oracle package is created when first referenced. Its initialization
>     code is run once (ie costly queries to populate session wide package
>     params) and the package dies at the end of the session
>     An analogy with OOP is that it's like having a single class instance
>     available for the duration of a session.
> 
>     SOME POWERFUL USES OF PACKAGES:
> 
>     1. Pipes - oracle dbms_pipe built-in allows asynchronous communication
>     between any number of producer/consumer database sessions on any
>     number of
>     pipes
> 
>     2. Logging - leave all logging/debug statements in code, decision on
>     logging output can be made when the logging package is initialised
>     (eg by
>     querying lookup tables for user, on/off, level, and
>     destination).  Combine
>     logging with pipes and the output can be stored in tables seperate from
>     the current transaction.  Include timing info down to milliseconds and
>     live problems/bottlenecks can more easily be identified.
> 
>     3. Batch reporting - more suited to autonomous transactions than logging
>     but useful to have the report package store start time, duration,
>     error/warning count running totals etc. and summarize automatically at
>     report end.
> 
>     See the example below on how to implement a version of the oracle
>     dbms_output package in plpython
> 
>     EXTENSIONS TO POSTGRES:
> 
>     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>;
> 
>     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?
> 
>     The language in which the package was created would process the 'package
>     code', for example in python:
>     o  create public functions linking header declaration to package
>     body code
>     (see dbms_output example)
>     o  process embedded sql, eg l_curs=select * from dual ->
>     l_curs=self.execute('select * from dual')
>     o  the extracted sql can be 'prepared' by postgres and syntax exceptions
>     reported as compilation errors
> 
>     SUMMARY:
>     Packages are an important addition to postgres.  Some of the server side
>     languages have the potential to create them now.  It would be useful to
>     add a common high level syntax before the various language
>     implementations
>     start developing their own solutions.
> 
>     I'm currently testing dbms_pipe on postgres, let me know if anyone is
>     interested.  I replaced xml-rpc (5 messages/second) by sockets (600x
>     faster!), and may test corba
> 
>     Ronnie Mackay
> 
>     -----------------------------------------------------------------------------
>     -----------------------------------------------------------------------------
> 
>     EXAMPLE :CONVERT ORACLE BUILT-IN PACKAGE DBMS_OUTPUT:
> 
>     [Oracle syntax is :exec dbms_output.put_line('line1');]
> 
>     Postgres>select dbms_output_put_line('line 1');
>     Postgres>select test_call_dbms_output_from_within_plpgsql('line 2
>     (plpgsql)');
>     Postgres>select test_call_dbms_output_from_within_plpython('line 3
>     (plpython)');
>     Postgres>select dbms_output_put_line('line 4');
> 
>     Postgres>select dbms_output_get_lines();
>                 --- DBMS_OUTPUT DEMO ---
>                 line 1
>                 line 2 (plpgsql)
>                 line 3 (plpython)
>                 line 4
>                 --- DBMS_OUTPUT DEMO ---
> 
>     So using current postgres syntax the only difference with oracle is that
>     dbms_output.put_line('line 1'); becomes
>     dbms_output_put_line('line 1');
>     The source code to implement the package body is returned by postgres
>     function dbms_output()
> 
>     POSTGRES CREATE STATEMENTS FOR EXAMPLE:
>     -----------------------------------------------------------------------------
> 
> 
>     CREATE or replace FUNCTION dbms_output_put_line(text) RETURNS text AS $$
>     from plpython import getPackage
>     return getPackage(GD, plpy, 'dbms_output').putLine(args[0])
>     $$ LANGUAGE plpythonu;
> 
>     CREATE or replace FUNCTION dbms_output_get_lines() RETURNS text AS $$
>     from plpython import getPackage
>     return getPackage(GD, plpy, 'dbms_output').getLines()
>     $$ LANGUAGE plpythonu;
> 
>     -- package body
>     CREATE OR REPLACE FUNCTION dbms_output() RETURNS text AS $$
>     return """
>     from plpython import PlPythonPackage
> 
>     class Package(PlPythonPackage):
> 
>        def __init__(self, in_plpy):
> 
>           PlPythonPackage.__init__(self, in_plpy)
>           self.lines=[]
> 
>        def putLine(self, in_text):
>           self.lines.append(in_text)
> 
>        def getLines(self):
>           l_lines=self._title()
>           l_lines+=self.lines
>           l_lines+=self._title()
>           self.lines=[]
>           return chr(10).join(l_lines)
> 
>        def _title(self):
>           return ['--- DBMS_OUTPUT DEMO ---']
>     """
>     $$ LANGUAGE plpythonu;
> 
>     CREATE OR REPLACE FUNCTION
>     test_call_dbms_output_from_within_plpython(in_text text) RETURNS
>     text AS
>     $$
>     from plpython import getPackage
>     dbms_output = getPackage(GD, plpy, 'dbms_output')
>     print dbms_output
>     print dir(dbms_output)
>     dbms_output.putLine(args[0])
>     $$ LANGUAGE plpythonu;
> 
>     CREATE or replace FUNCTION
>     test_call_dbms_output_from_within_plpgsql(in_text text) RETURNS text
>     AS $$
>     declare
>        dummy text;
>     BEGIN
>        dummy := dbms_output_put_line(in_text);
>        return '';
>     END;
>     $$ LANGUAGE plpgsql;
> 
>     -----------------------------------------------------------------------------
> 
> 
>     PYTHON MODULE (plpython.PlPythonPackage):
>     -----------------------------------------------------------------------------
> 
>     import imp, sys
> 
>     class PlPythonPackage:
>        """ Base class for postgres emulation of oracle package structure in
>     PlPython """
> 
>        def __init__(self, in_plpy):
>           self.plpy=in_plpy
>           l_row=self.plpy.execute('select current_user as user,
>     current_database() as database')[0]
>           self.user=l_row ["user"]
>           self.database=l_row["database"]
> 
>        def execute(self, in_sql):
>           l_result = self.plpy.execute(in_sql)
> 
>     def getPackage(in_gd, in_plpy, in_package):
>        """ Dynamically load plpython package"""
>        try:
>           return in_gd[in_package]
>        except KeyError:
>           l_result=in_plpy.execute('select %s()'%in_package)
>           l_code=l_result[0].popitem()[1].replace('\n\t','\n')
>           l_module = imp.new_module (in_package)
>           exec l_code in l_module.__dict__
>           l_package=l_module.Package(in_plpy)
>           in_gd[in_package]=l_package
>           return l_package
> 
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 1: subscribe and unsubscribe commands go to
>     majordomo@postgresql.org <mailto:majordomo@postgresql.org>
> 
> 


-- 
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/