Oracle Style packages on postgres

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