Re: Oracle Style packages on postgres

One simple benefit to packages is just organization of related code.

On 5/7/05, rmm(at)sqlisor(dot)com <rmm(at)sqlisor(dot)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(at)postgresql(dot)org
>