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 [EMAIL PROTECTED]