Re: Oracle Style packages on postgres
От | Satoshi Nagayasu |
---|---|
Тема | Re: Oracle Style packages on postgres |
Дата | |
Msg-id | 427EE0FE.8090607@nttdata.co.jp обсуждение исходный текст |
Ответ на | Re: Oracle Style packages on postgres (Bob <luckyratfoot@gmail.com>) |
Ответы |
Re: Oracle Style packages on postgres
(Thomas Hallgren <thhal@mailblocks.com>)
|
Список | 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/
В списке pgsql-hackers по дате отправления: