Thread

Oracle Style packages on postgres

rmm@sqlisor.com — 2005-05-07T11:00:56Z

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

Re: Oracle Style packages on postgres

Bob <luckyratfoot@gmail.com> — 2005-05-09T03:38:41Z

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

On 5/7/05, rmm@sqlisor.com <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
>

Re: Oracle Style packages on postgres

Satoshi Nagayasu <nagayasus@nttdata.co.jp> — 2005-05-09T04:03:10Z

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/

Re: Oracle Style packages on postgres

Thomas Hallgren <thhal@mailblocks.com> — 2005-05-09T05:36:21Z

Satoshi Nagayasu wrote:

>>    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.
>>
PL/Java has an object called "Session" that does exactly this. It is not
available from other languages at present. Are Packages supposed to be
cross-language?

Regards,
Thomas Hallgren

Re: Oracle Style packages on postgres

Hannu Krosing <hannu@skype.net> — 2005-05-09T11:33:47Z

On E, 2005-05-09 at 07:36 +0200, Thomas Hallgren wrote:
> Satoshi Nagayasu wrote:
> 
> >>    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.
> >>
> PL/Java has an object called "Session" that does exactly this.

And pl/python has a global dictionary SD for the same purpose.

> It is not
> available from other languages at present. Are Packages supposed to be
> cross-language?

Probably not, as they already have most of the needed features.

Maybe we can set up some lighter version of package for cross-language
features (like installing removing a group of functions) but this are
much less needed for more advanced languages.

-- 
Hannu Krosing <hannu@skype.net>

Re: Oracle Style packages on postgres

decibel <decibel@decibel.org> — 2005-05-09T17:01:51Z

On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote:
> One simple benefit to packages is just organization of related code.

Which, IMHO, is greatly diminished by the lack of
schema.package.function notation. BTW, the original post referred to
this as user.package.function, but I believe that technically it's
actually schema.package.function (Oracle tends to mix schemas and
users). In any case, schema.package.function is what would make sense in
PostgreSQL.

Personally, I think the biggest win here would be adding package support
and syntax to plpgsql. Not only would it make porting from Oracle
easier, it would also make plpgsql much, much more powerful.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Oracle Style packages on postgres

Josh Berkus <josh@agliodbs.com> — 2005-05-09T17:05:38Z

Rmm,

>    "A black box processing engine with one or more public access functions
> that retains state across calls"

In other words, an Object.   <grin>

> 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>;

Hmmm.  What about package variables?   For me, this is one of the most 
valuable parts of packages.  

I've also never much liked Oracle's seperate package_header and package_body 
declaration structure: if the two are intrinsically tied, why not make it one 
declaration?   Is syntactical compatibility important enough that we need to 
imitate their design errors?

> 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?

Yes, actually.  If you look at the discussion, this is what killed the 2001 
proposal; packages were proposed as orthagonal to schema which was not 
acceptable.  

However, now that schema are well established, it seems like this namespace 
issue is limited.  The problem would be that you'd have to make sure that no 
two schema and packages had the same name, or that there would be an 
automatic precedence of shema, package established.  

So, given a shema named "dataloader" and a package named "dataloader" and a 
function named "copy_it(filename)", what would happen is:

dataloader.dataloader.copy_it('/tmp/somefile') 
     ... would be absolutely clear
dataloader.copy_it('/tmp/somefile')
     ... would attempt to call the copy_it function in the dataloader 
*schema*, not the dataloader *package*.   

The above seems inevitable, and not really a problem to me.   We simply warn 
people in the docs of the behavior, and to avoid duplicate naming.

I think there are more important questions: 

1) how do you prevent users from executing the package functions outside of 
the package?
2) Have you taken care of package variables?   If so, are they only 
per-session, or global?   If they are global, how do you accomplish this?
3) For that matter, is initialization per session or global?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Oracle Style packages on postgres

Jonah H. Harris <jharris@tvi.edu> — 2005-05-09T17:19:15Z

I agree wholeheartedly and was actually just thinking of this yesterday.

Back when I was working on NEXTGRES I implemented package support into 
plpgsql including scopes.  While my time is pretty tight right now, I'd 
be more than willing to work with whoever the plpgsql master is.

Jim C. Nasby wrote:

>On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote:
>  
>
>>One simple benefit to packages is just organization of related code.
>>    
>>
>
>Which, IMHO, is greatly diminished by the lack of
>schema.package.function notation. BTW, the original post referred to
>this as user.package.function, but I believe that technically it's
>actually schema.package.function (Oracle tends to mix schemas and
>users). In any case, schema.package.function is what would make sense in
>PostgreSQL.
>
>Personally, I think the biggest win here would be adding package support
>and syntax to plpgsql. Not only would it make porting from Oracle
>easier, it would also make plpgsql much, much more powerful.
>  
>

Inline PL/pgSQL

Jonah H. Harris <jharris@tvi.edu> — 2005-05-09T17:44:23Z

Hey everyone,

In addition to package support in plpgsql, it would be really handy to 
have inline plpgsql.  Likewise, I think there are others who feel this 
way as-well.

Years ago, Oracle merged PL/SQL with their normal SQL parser which 
allowed for inline PL/SQL.  They did this because it was difficult to 
maintain two separate parsers.  While this worked great for Oracle, it 
probably wouldn't really work as well for PostgreSQL because pgsql 
supports multiple procedural languages.

As for implementation, I think it would obviously be best to leave 
plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the 
normal parser and have the system generate/execute a function on the 
fly.  Or, maybe it would be better to integrate plpgsql.  Or, I may just 
be crazy.

Would anyone else ever benefit from inline functions?  Does anyone have 
any ideas about implementation?  Please shoot your opinions this way.  
Thanks.

-Jonah

Re: Inline PL/pgSQL

Joshua D. Drake <jd@commandprompt.com> — 2005-05-09T17:58:27Z

> 
> As for implementation, I think it would obviously be best to leave 
> plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the 
> normal parser and have the system generate/execute a function on the 
> fly.  Or, maybe it would be better to integrate plpgsql.  Or, I may just 
> be crazy.
> 
> Would anyone else ever benefit from inline functions?

Well I could see inline functions being useful for debugging a function 
during development but I don't think I would want a bunch of plPGSQL 
mucking up my pretty SQL :)

Sincerely,

Joshua D. Drake




-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Inline PL/pgSQL

Josh Berkus <josh@agliodbs.com> — 2005-05-09T18:08:13Z

Jonah,

> In addition to package support in plpgsql, it would be really handy to
> have inline plpgsql.  Likewise, I think there are others who feel this
> way as-well.

I think a number of people would be interested in this.   However, your 
biggest development issue, as I've been told, is that the Pl/pgSQL parser 
isn't nearly as mature as the SQL parser.   So an overhaul of the PL/pgSQL 
backend code would need to precede any merger of the two parsers.

Also, take a look at the "pl/pgsql enabled by default" thread on this mailing 
list for security concerns.   These security concerns would be much more 
significant if plpgsql were automatically available on the command line.   As 
such, you'd need to make building it in to the SQL command line a 
compile-time option so that security-conscious admins could disable it if 
they want to.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Inline PL/pgSQL

Andrew Dunstan <andrew@dunslane.net> — 2005-05-09T18:18:10Z


Joshua D. Drake wrote:

>>
>> As for implementation, I think it would obviously be best to leave 
>> plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the 
>> normal parser and have the system generate/execute a function on the 
>> fly.  Or, maybe it would be better to integrate plpgsql.  Or, I may 
>> just be crazy.
>>
>> Would anyone else ever benefit from inline functions?
>
>
> Well I could see inline functions being useful for debugging a 
> function during development but I don't think I would want a bunch of 
> plPGSQL mucking up my pretty SQL :)
>
>

Then don't put it there ;-)

I think you'd need to do something like this:

PERFORM language plpgsql $$

  -- some plpgsql stuff here

$$;

cheers

andrew

Re: Oracle Style packages on postgres

decibel <decibel@decibel.org> — 2005-05-09T18:46:33Z

On Mon, May 09, 2005 at 10:05:38AM -0700, Josh Berkus wrote:
> I've also never much liked Oracle's seperate package_header and package_body 
> declaration structure: if the two are intrinsically tied, why not make it one 
> declaration?   Is syntactical compatibility important enough that we need to 
> imitate their design errors?

Actually, there is a notable difference between the two. Replacing the
body of a package has a minimal impact on the database, but replacing
the header requires more work to invalidate cached stuff. I think
there's also a few other side effects.

This isn't to say that this is a good way to handle this, but I believe
it's why Oracle does it.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Oracle Style packages on postgres

Bruce Momjian <pgman@candle.pha.pa.us> — 2005-05-09T18:50:19Z

I would be interested in hearing how we can implement Oracle packages in
a way that seamlessly integrates into what we have.  Is it like
functions that are automatically called when a schema is accessed?  And
the result put into a per-session temporary schema?

I think it is unlikely we would implement Oracle packages exactly like
Oracle but I think there is interest in adding that functionality to
PostgreSQL.

If we can work up a list I can add it to the TODO list.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Rmm,
> 
> >    "A black box processing engine with one or more public access functions
> > that retains state across calls"
> 
> In other words, an Object.   <grin>
> 
> > 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>;
> 
> Hmmm.  What about package variables?   For me, this is one of the most 
> valuable parts of packages.  
> 
> I've also never much liked Oracle's seperate package_header and package_body 
> declaration structure: if the two are intrinsically tied, why not make it one 
> declaration?   Is syntactical compatibility important enough that we need to 
> imitate their design errors?
> 
> > 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?
> 
> Yes, actually.  If you look at the discussion, this is what killed the 2001 
> proposal; packages were proposed as orthagonal to schema which was not 
> acceptable.  
> 
> However, now that schema are well established, it seems like this namespace 
> issue is limited.  The problem would be that you'd have to make sure that no 
> two schema and packages had the same name, or that there would be an 
> automatic precedence of shema, package established.  
> 
> So, given a shema named "dataloader" and a package named "dataloader" and a 
> function named "copy_it(filename)", what would happen is:
> 
> dataloader.dataloader.copy_it('/tmp/somefile') 
>      ... would be absolutely clear
> dataloader.copy_it('/tmp/somefile')
>      ... would attempt to call the copy_it function in the dataloader 
> *schema*, not the dataloader *package*.   
> 
> The above seems inevitable, and not really a problem to me.   We simply warn 
> people in the docs of the behavior, and to avoid duplicate naming.
> 
> I think there are more important questions: 
> 
> 1) how do you prevent users from executing the package functions outside of 
> the package?
> 2) Have you taken care of package variables?   If so, are they only 
> per-session, or global?   If they are global, how do you accomplish this?
> 3) For that matter, is initialization per session or global?
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Oracle Style packages on postgres

Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-09T19:24:28Z

Josh Berkus <josh@agliodbs.com> writes:
> Yes, actually.  If you look at the discussion, this is what killed the 2001 
> proposal; packages were proposed as orthagonal to schema which was not 
> acceptable.  

I think what actually killed that proposal was that it was not made
clear what it did that wouldn't be done as well (and in a more standard
fashion) by providing schemas.

What I read in this thread is that the only truly missing feature is
package variables (ie, session-local variables); is that an accurate
statement?  If so, it would seem simplest to add such a feature to
plpgsql and be done with it.  Several people already pointed out that
most of the other PLs support that feature today.

			regards, tom lane

Re: Oracle Style packages on postgres

Josh Berkus <josh@agliodbs.com> — 2005-05-09T19:30:40Z

Tom,

> What I read in this thread is that the only truly missing feature is
> package variables (ie, session-local variables); is that an accurate
> statement?  If so, it would seem simplest to add such a feature to
> plpgsql and be done with it.  Several people already pointed out that
> most of the other PLs support that feature today.

Also initialization, namespacing, and security.   The ability to "package" 
bunches of functions, and only allow their calling in the context of a 
package, is quite valuable in installations which support 1,000's of 
procedures.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Oracle Style packages on postgres

Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-09T19:33:24Z

Josh Berkus <josh@agliodbs.com> writes:
>> What I read in this thread is that the only truly missing feature is
>> package variables (ie, session-local variables); is that an accurate
>> statement?  If so, it would seem simplest to add such a feature to
>> plpgsql and be done with it.  Several people already pointed out that
>> most of the other PLs support that feature today.

> Also initialization, namespacing, and security.   The ability to "package" 
> bunches of functions, and only allow their calling in the context of a 
> package, is quite valuable in installations which support 1,000's of 
> procedures.

This is exactly the sort of argumentation that got the last proposal
shot down ;-).  I see no reason that you can't do the namespacing and
security as well or better using the existing (and more standard) schema
feature.  If there's something there that's not covered, what is it?

(The initialization bit goes along with the variables, AFAICS.)

			regards, tom lane

Re: Inline PL/pgSQL

Hannu Krosing <hannu@tm.ee> — 2005-05-09T19:39:38Z

On E, 2005-05-09 at 11:44 -0600, Jonah H. Harris wrote:
> Hey everyone,
> 
> In addition to package support in plpgsql, it would be really handy to 
> have inline plpgsql.  Likewise, I think there are others who feel this 
> way as-well.

Session variables is what I miss most.

> Years ago, Oracle merged PL/SQL with their normal SQL parser which 
> allowed for inline PL/SQL.  

Was that really that much time ago ? IIRC this was fanfared as one of
big advancements of Oracle 10.

> They did this because it was difficult to maintain two separate parsers. 

Also they claimed that this cleared away some subtle differences in the
languages supported by SQL and pl/SQL.

>  While this worked great for Oracle, it 
> probably wouldn't really work as well for PostgreSQL because pgsql 
> supports multiple procedural languages.

AFAIK Oracle also supports at least java, using a syntax somewhat
similar to ours.

> As for implementation, I think it would obviously be best to leave 
> plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the 
> normal parser and have the system generate/execute a function on the 
> fly.  Or, maybe it would be better to integrate plpgsql.  Or, I may just 
> be crazy.

Just having $$ quoting and named arguments does most of what I need for
using functions from a command line. If pl/pgsql and perhaps even plain
sql get session variables, preferrably usable by both (and in future
accessible from other) pl-s that would cover most of my needs.

> Would anyone else ever benefit from inline functions?  Does anyone have 
> any ideas about implementation?  Please shoot your opinions this way.  

While I can imagine how to use a declarative language from procedural
one (pl/pgsql using sql) I have much harder time to imagine how to do
the opposite in a convevient way.

something like this ? :

select $$ inline scalar plpgsql func here $$, count(*) 
  from $$ inline set function here $$ sub
 group by 1;

perhaps just supporting TEMP funcions should be enough ?

-- 
Hannu Krosing <hannu@tm.ee>

Re: Oracle Style packages on postgres

Josh Berkus <josh@agliodbs.com> — 2005-05-09T19:43:01Z

Tom,

> This is exactly the sort of argumentation that got the last proposal
> shot down ;-).  I see no reason that you can't do the namespacing and
> security as well or better using the existing (and more standard) schema
> feature.  If there's something there that's not covered, what is it?

a) When you have 1000's of procedures, it becomes very useful to have more 
than one level of namespacing.   This is not an exaggeration; one project I 
looked at who decided not to convert from Oracle to PostgreSQL had over 
100,000 procedures and functions.   Lack of packages was their main reason 
for not switching.  Schemas provide only *one* level of namespacing, unless 
we want to "improve" on the SQL standard and allow nested schemas.

b) Schemas do not provide us with any way of limiting the scope of functions 
and persistent variables.  With packages, you would want:
	1. functions which can only be called internally to the package
	2. variables which are only visible inside the package
	3. functions which can only be called as part of the package (thus utilizing 
the initialization and internal variables) and not on their own.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Inline PL/pgSQL

David Fetter <david@fetter.org> — 2005-05-09T19:43:50Z

On Mon, May 09, 2005 at 11:44:23AM -0600, Jonah H. Harris wrote:
> Hey everyone,
> 
> In addition to package support in plpgsql, it would be really handy
> to have inline plpgsql.  Likewise, I think there are others who feel
> this way as-well.

Why yes, there are. :)

> Years ago, Oracle merged PL/SQL with their normal SQL parser which
> allowed for inline PL/SQL.  They did this because it was difficult
> to maintain two separate parsers.  While this worked great for
> Oracle, it probably wouldn't really work as well for PostgreSQL
> because pgsql supports multiple procedural languages.

I proposed a syntax for this awhile back.  I haven't found it in the
archives, but it goes like this:

EXECUTE IMMEDIATE $$
    function body here
$$
LANGUAGE plfoo;

Similarly, a CREATE TEMPORARY FUNCTION could be quite handy.

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: Oracle Style packages on postgres

Thomas Hallgren <thhal@mailblocks.com> — 2005-05-09T21:24:45Z

Josh Berkus wrote:
> Tom,
> 
> 
>>This is exactly the sort of argumentation that got the last proposal
>>shot down ;-).  I see no reason that you can't do the namespacing and
>>security as well or better using the existing (and more standard) schema
>>feature.  If there's something there that's not covered, what is it?
> 
> 
> a) When you have 1000's of procedures, it becomes very useful to have more 
> than one level of namespacing.   This is not an exaggeration; one project I 
> looked at who decided not to convert from Oracle to PostgreSQL had over 
> 100,000 procedures and functions.   Lack of packages was their main reason 
> for not switching.  Schemas provide only *one* level of namespacing, unless 
> we want to "improve" on the SQL standard and allow nested schemas.
> 
> b) Schemas do not provide us with any way of limiting the scope of functions 
> and persistent variables.  With packages, you would want:
> 	1. functions which can only be called internally to the package
> 	2. variables which are only visible inside the package
> 	3. functions which can only be called as part of the package (thus utilizing 
> the initialization and internal variables) and not on their own.
> 
What Josh describes here are excellent features but IMHO, the Oracle 
PACKAGE concept is an abomination that should have been left out. The 
reason I say this is that Oracle also provide the ability to create user 
defined types that have methods. Both instance and static methods can be 
created.

In Oracle you can use the syntax:

<schema>.<package>.<function>()

but you can just as well use the syntax:

<schema>.<type>.<static method>()

Why do you need both? If PostgreSQL is going to add new nice features 
that enables better namespace handling and global variables, take a look 
at Oracles UDT's with static and instance methods. Only thing that I'm 
not sure is there is static variables. If it's missing, we could add 
that easilly and give them the same life-span as the session.

A UDT can be exchanged seamlessly across PL's so it would become a 
really elegant solution for session variables.

Regards,
Thomas Hallgren

Re: Oracle Style packages on postgres

Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-09T21:44:08Z

Thomas Hallgren <thhal@mailblocks.com> writes:
> In Oracle you can use the syntax:
> <schema>.<package>.<function>()
> but you can just as well use the syntax:
> <schema>.<type>.<static method>()

Hmm.  I think there is also something pretty close to that in SQL2003.
It would be a lot easier to talk us into accepting something that's in
the spec than something that isn't.

			regards, tom lane

Re: Inline PL/pgSQL

Jonah H. Harris <jharris@tvi.edu> — 2005-05-09T23:28:42Z

David,

I agree with your idea.  That seems like it would be somewhat easy to 
implement and would do exactly what I would need.  Anyone else have 
ideas or thoughts along this line?

David Fetter wrote:

>On Mon, May 09, 2005 at 11:44:23AM -0600, Jonah H. Harris wrote:
>  
>
>>Hey everyone,
>>
>>In addition to package support in plpgsql, it would be really handy
>>to have inline plpgsql.  Likewise, I think there are others who feel
>>this way as-well.
>>    
>>
>
>Why yes, there are. :)
>
>  
>
>>Years ago, Oracle merged PL/SQL with their normal SQL parser which
>>allowed for inline PL/SQL.  They did this because it was difficult
>>to maintain two separate parsers.  While this worked great for
>>Oracle, it probably wouldn't really work as well for PostgreSQL
>>because pgsql supports multiple procedural languages.
>>    
>>
>
>I proposed a syntax for this awhile back.  I haven't found it in the
>archives, but it goes like this:
>
>EXECUTE IMMEDIATE $$
>    function body here
>$$
>LANGUAGE plfoo;
>
>Similarly, a CREATE TEMPORARY FUNCTION could be quite handy.
>
>Cheers,
>D
>  
>

Re: Inline PL/pgSQL

David Fetter <david@fetter.org> — 2005-05-10T00:26:35Z

On Mon, May 09, 2005 at 05:28:42PM -0600, Jonah H. Harris wrote:
> David,
> 
> I agree with your idea.  That seems like it would be somewhat easy to 
> implement and would do exactly what I would need.  Anyone else have 
> ideas or thoughts along this line?

Seeing as EXECUTE IMMEDIATE is already used, so how about PERFORM
IMMEDIATE?  I also like the idea of functions whose scope is settable.
Something like this:

CREATE [OR REPLACE] [ TRANSACTION | SESSION ] FUNCTION ...

Cheers,
D
> 
> David Fetter wrote:
> 
> >On Mon, May 09, 2005 at 11:44:23AM -0600, Jonah H. Harris wrote:
> > 
> >
> >>Hey everyone,
> >>
> >>In addition to package support in plpgsql, it would be really handy
> >>to have inline plpgsql.  Likewise, I think there are others who feel
> >>this way as-well.
> >>   
> >>
> >
> >Why yes, there are. :)
> >
> > 
> >
> >>Years ago, Oracle merged PL/SQL with their normal SQL parser which
> >>allowed for inline PL/SQL.  They did this because it was difficult
> >>to maintain two separate parsers.  While this worked great for
> >>Oracle, it probably wouldn't really work as well for PostgreSQL
> >>because pgsql supports multiple procedural languages.
> >>   
> >>
> >
> >I proposed a syntax for this awhile back.  I haven't found it in the
> >archives, but it goes like this:
> >
> >EXECUTE IMMEDIATE $$
> >   function body here
> >$$
> >LANGUAGE plfoo;
> >
> >Similarly, a CREATE TEMPORARY FUNCTION could be quite handy.
> >
> >Cheers,
> >D
> > 
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: Inline PL/pgSQL

Neil Conway <neilc@samurai.com> — 2005-05-10T00:37:04Z

David Fetter wrote:
> EXECUTE IMMEDIATE $$
>     function body here
> $$
> LANGUAGE plfoo;

Seems like a lot of unnecessary syntax for something that would be 
manually used by a lot of DBAs. Also, this is unrelated to normal 
EXECUTE, or the EXECUTE IMMEDIATE defined by the standard, so I'm not 
sure it's a good idea to use similar syntax.

BTW, this is a little off-the-wall, but one interesting idea to help SQL 
and PL/foo integration would be to replace the bison grammar for SQL 
with a hand-written recursive descent parser. If written carefully, this 
would allow other procedural languages to "call into" the SQL parser 
from their own parsers when appropriate, and integrate the resulting 
parse nodes into their own parse tree. PL/PgSQL in HEAD does something a 
bit similar for syntax checking, but it's pretty ugly (we need to 
manually invoke raw_parser(), and then we throw away the result).

-Neil

Re: Oracle Style packages on postgres

Bruce Momjian <pgman@candle.pha.pa.us> — 2005-05-10T01:28:07Z

Josh Berkus wrote:
> Tom,
> 
> > This is exactly the sort of argumentation that got the last proposal
> > shot down ;-).  I see no reason that you can't do the namespacing and
> > security as well or better using the existing (and more standard) schema
> > feature.  If there's something there that's not covered, what is it?
> 
> a) When you have 1000's of procedures, it becomes very useful to have more 
> than one level of namespacing.   This is not an exaggeration; one project I 
> looked at who decided not to convert from Oracle to PostgreSQL had over 
> 100,000 procedures and functions.   Lack of packages was their main reason 
> for not switching.  Schemas provide only *one* level of namespacing, unless 
> we want to "improve" on the SQL standard and allow nested schemas.
> 
> b) Schemas do not provide us with any way of limiting the scope of functions 
> and persistent variables.  With packages, you would want:
> 	1. functions which can only be called internally to the package
> 	2. variables which are only visible inside the package
> 	3. functions which can only be called as part of the package (thus utilizing 
> the initialization and internal variables) and not on their own.

What if we defined functions to look in their own schemas for functions
they call, then use the search_path, rather than using the search path
first?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Oracle Style packages on postgres

Josh Berkus <josh@agliodbs.com> — 2005-05-10T01:46:52Z

Bruce,

> > b) Schemas do not provide us with any way of limiting the scope of
> > functions and persistent variables.  With packages, you would want:
> > 	1. functions which can only be called internally to the package
> > 	2. variables which are only visible inside the package
> > 	3. functions which can only be called as part of the package (thus
> > utilizing the initialization and internal variables) and not on their
> > own.
>
> What if we defined functions to look in their own schemas for functions
> they call, then use the search_path, rather than using the search path
> first?

That really doesn't address the desired functionality.  For example, I could 
have a package whose initialization function involves some security checks, 
and then the package's "methods" (internal functions) would access the 
variables set by the security check function ... but those variables would 
NOT be available to the user or modifiable by them.

I know the need for this is probably hypothetical to a lot of -hackers, but 
it's pretty common programming in the Oracle PL/SQL world.  

Of course, if there's something in SQL2003 that supports this, it would be 
really keen to know it ...

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Oracle Style packages on postgres

Bruce Momjian <pgman@candle.pha.pa.us> — 2005-05-10T01:49:24Z

Josh Berkus wrote:
> Bruce,
> 
> > > b) Schemas do not provide us with any way of limiting the scope of
> > > functions and persistent variables.  With packages, you would want:
> > > 	1. functions which can only be called internally to the package
> > > 	2. variables which are only visible inside the package
> > > 	3. functions which can only be called as part of the package (thus
> > > utilizing the initialization and internal variables) and not on their
> > > own.
> >
> > What if we defined functions to look in their own schemas for functions
> > they call, then use the search_path, rather than using the search path
> > first?
> 
> That really doesn't address the desired functionality.  For example, I could 
> have a package whose initialization function involves some security checks, 
> and then the package's "methods" (internal functions) would access the 
> variables set by the security check function ... but those variables would 
> NOT be available to the user or modifiable by them.
> 
> I know the need for this is probably hypothetical to a lot of -hackers, but 
> it's pretty common programming in the Oracle PL/SQL world.  
> 
> Of course, if there's something in SQL2003 that supports this, it would be 
> really keen to know it ...

Agreed, but saying we are going to just go out and implement everything
Oracle packages have just because they have them isn't likely to happen
for PostgreSQL.  We need a list of things that need to be added, and how
our existing functionality will be modified to make them available.

Just saying "we need Oracle packages" doesn't make it happen.  I have
followed the discussion and I still don't have a clear idea of the exact
additions that people want, and without that, nothing is likely to
happen.  I don't even have something for the TODO list at this point.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Oracle Style packages on postgres

Josh Berkus <josh@agliodbs.com> — 2005-05-10T01:53:14Z

B-

> Just saying "we need Oracle packages" doesn't make it happen.  I have
> followed the discussion and I still don't have a clear idea of the exact
> additions that people want, and without that, nothing is likely to
> happen.  I don't even have something for the TODO list at this point.

That's what I'm trying to help define.

I think that private variables and private functions need to be part of the 
definition.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Oracle Style packages on postgres

Bruce Momjian <pgman@candle.pha.pa.us> — 2005-05-10T01:56:53Z

Josh Berkus wrote:
> B-
> 
> > Just saying "we need Oracle packages" doesn't make it happen. ?I have
> > followed the discussion and I still don't have a clear idea of the exact
> > additions that people want, and without that, nothing is likely to
> > happen. ?I don't even have something for the TODO list at this point.
> 
> That's what I'm trying to help define.
> 
> I think that private variables and private functions need to be part of the 
> definition.

OK, so it seems we need:

	C static/private functions for schemas
	C static/private variables for schemas

Are private variables implemented via the temporary per-session schema?

	nested schemas

What does the standard say?

Is that it?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Inline PL/pgSQL

Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-10T03:20:15Z

Neil Conway <neilc@samurai.com> writes:
> BTW, this is a little off-the-wall, but one interesting idea to help SQL 
> and PL/foo integration would be to replace the bison grammar for SQL 
> with a hand-written recursive descent parser.

Ick.  I gave up hand-written RD parsers twenty-five years ago.  They are
tedious to write, error-prone (due to the utter lack of any checking
that what you wrote embodies the syntax you meant), less flexible than
LALR(1), and generally have few redeeming social advantages.

Which is not to say that plpgsql in its current implementation isn't
mighty ugly too.  I'd dearly love to find a better solution ... but
"throw away the parser generator" isn't a better solution.

			regards, tom lane

Re: Oracle Style packages on postgres

Josh Berkus <josh@agliodbs.com> — 2005-05-10T03:34:22Z

Bruce,

> OK, so it seems we need:
>
> 	C static/private functions for schemas
> 	C static/private variables for schemas
>
> Are private variables implemented via the temporary per-session schema?
>
> 	nested schemas
>
> What does the standard say?
>
> Is that it?

Hmmm.  That's an interesting approach.  I, personally, would buy that.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Oracle Style packages on postgres

Thomas Hallgren <thhal@mailblocks.com> — 2005-05-10T08:29:18Z

Bruce Momjian wrote:
> Josh Berkus wrote:
> 
>>I think that private variables and private functions need to be part of the 
>>definition.
> 
> 
> OK, so it seems we need:
> 
> 	C static/private functions for schemas
> 	C static/private variables for schemas
> 
> Are private variables implemented via the temporary per-session schema?
> 
> 	nested schemas
> 
> What does the standard say?
> 
The standard says that rather then using nested schemas or packages in 
conjunction with functions in order to group functions with data, use 
user defined types with attributes and methods. Methods can be STATIC, 
INSTANCE or CONSTRUCTOR.

AFAICS there's nothing in the standard that alters the visibility of 
methods and attributes, so "private" is not covered (and don't recall 
that Oracle has that kind of visibility control either). Normal access 
restrictions apply of course.

I can't find any mention of schema variables. I think all life-cycle 
management of data is reduced to table storage. And why not? A temporary 
table can be viewed as session data right?

Using a KISS approach, the easiest thing to do that also would bring us 
closer to the standard, is to extend the notion of user defined types to 
include methods and conclude that storing session data in other ways 
than using temporary tables should be PL specific.

Regards,
Thomas Hallgren

Re: Oracle Style packages on postgres

Adrian Maier <adrian.maier@gmail.com> — 2005-05-10T09:01:54Z

---------- Forwarded message ----------
From: Adrian Maier <adrian.maier@gmail.com>
Date: May 10, 2005 12:01 PM
Subject: Re: [HACKERS] Oracle Style packages on postgres
To: "Jim C. Nasby" <decibel@decibel.org>


On 5/9/05, Jim C. Nasby <decibel@decibel.org> wrote:
> On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote:
> > One simple benefit to packages is just organization of related code.
>
> Which, IMHO, is greatly diminished by the lack of
> schema.package.function notation. BTW, the original post referred to
> this as user.package.function, but I believe that technically it's
> actually schema.package.function (Oracle tends to mix schemas and
> users). In any case, schema.package.function is what would make sense in
> PostgreSQL.
>
> Personally, I think the biggest win here would be adding package support
> and syntax to plpgsql. Not only would it make porting from Oracle
> easier, it would also make plpgsql much, much more powerful.

Hello,

What do you think about having some kind of language-independent
packages ?
I'm thinking that it could be handy to implement some functions in
plpgsql, some functions in plpython and so . And then bundle them
together into the same package.

Cheers,
Adrian Maier

Re: Oracle Style packages on postgres

Daniel Schuchardt <daniel_schuchardt@web.de> — 2005-05-10T11:55:01Z

Bruce Momjian schrieb:

>OK, so it seems we need:
>
>	C static/private functions for schemas
>	C static/private variables for schemas
>
>Are private variables implemented via the temporary per-session schema?
>
>	nested schemas
>
>What does the standard say?
>
>Is that it?
>
>  
>
Yeah,

that would be great. And don't forget global variables for pl/pgsql.

Daniel

Re: Oracle Style packages on postgres

Bruce Momjian <pgman@candle.pha.pa.us> — 2005-05-10T13:43:15Z

Thomas Hallgren wrote:
> Bruce Momjian wrote:
> > Josh Berkus wrote:
> > 
> >>I think that private variables and private functions need to be part of the 
> >>definition.
> > 
> > 
> > OK, so it seems we need:
> > 
> > 	C static/private functions for schemas
> > 	C static/private variables for schemas
> > 
> > Are private variables implemented via the temporary per-session schema?
> > 
> > 	nested schemas
> > 
> > What does the standard say?
> > 
> The standard says that rather then using nested schemas or packages in 
> conjunction with functions in order to group functions with data, use 
> user defined types with attributes and methods. Methods can be STATIC, 
> INSTANCE or CONSTRUCTOR.

So it sounds like you group the functions into user-defined types,
rather than nested schemas.  So you assocate functions with a table?

> AFAICS there's nothing in the standard that alters the visibility of 
> methods and attributes, so "private" is not covered (and don't recall 
> that Oracle has that kind of visibility control either). Normal access 
> restrictions apply of course.
> 
> I can't find any mention of schema variables. I think all life-cycle 
> management of data is reduced to table storage. And why not? A temporary 
> table can be viewed as session data right?
> 
> Using a KISS approach, the easiest thing to do that also would bring us 
> closer to the standard, is to extend the notion of user defined types to 
> include methods and conclude that storing session data in other ways 
> than using temporary tables should be PL specific.

I suppose.  I think we should focus on the use cases for Oracle
packages, rather than the specific functionality it provides.  What
things do people need PostgreSQL to do that it already doesn't do?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Oracle Style packages on postgres

Thomas Hallgren <thhal@mailblocks.com> — 2005-05-10T14:34:23Z

Bruce Momjian wrote:

>Thomas Hallgren wrote:
>  
>
>>Bruce Momjian wrote:
>>    
>>
>>>Josh Berkus wrote:
>>>
>>>      
>>>
>>>>I think that private variables and private functions need to be part of the 
>>>>definition.
>>>>        
>>>>
>>>OK, so it seems we need:
>>>
>>>	C static/private functions for schemas
>>>	C static/private variables for schemas
>>>
>>>Are private variables implemented via the temporary per-session schema?
>>>
>>>	nested schemas
>>>
>>>What does the standard say?
>>>
>>>      
>>>
>>The standard says that rather then using nested schemas or packages in 
>>conjunction with functions in order to group functions with data, use 
>>user defined types with attributes and methods. Methods can be STATIC, 
>>INSTANCE or CONSTRUCTOR.
>>    
>>
>
>So it sounds like you group the functions into user-defined types,
>rather than nested schemas.
>
Yes, you'd get <schema>.<type>.<method> as the fully qualified name.

>  So you assocate functions with a table?
>  
>
Not necessarily a table. A type is just a type. A type containing just 
static methods is not very different from an Oracle package. A table 
created from a type may of course have methods associated with it. That 
gets really interesting when you use INSTANCE methods. They act on a per 
row basis so that you can do things like:

SELECT x.someMethod() FROM someTable x;

rather than as today.

SELECT someFunction(x) FROM someTable x;

Regards,
Thomas Hallgren

Re: Oracle Style packages on postgres

decibel <decibel@decibel.org> — 2005-05-10T17:45:47Z

On Tue, May 10, 2005 at 12:01:54PM +0300, Adrian Maier wrote:
> > Personally, I think the biggest win here would be adding package support
> > and syntax to plpgsql. Not only would it make porting from Oracle
> > easier, it would also make plpgsql much, much more powerful.
> 
> Hello,
> 
> What do you think about having some kind of language-independent
> packages ?
> I'm thinking that it could be handy to implement some functions in
> plpgsql, some functions in plpython and so . And then bundle them
> together into the same package.

Personally, I basically only use plpgsql, but I can certainly see where
there would be value in being able to include functions and procedures
from multiple languages in one package. But I suspect this will also
make some things more difficult, such as global static variables.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Oracle Style packages on postgres

decibel <decibel@decibel.org> — 2005-05-10T17:55:00Z

On Mon, May 09, 2005 at 11:24:45PM +0200, Thomas Hallgren wrote:
> In Oracle you can use the syntax:
> 
> <schema>.<package>.<function>()
> 
> but you can just as well use the syntax:
> 
> <schema>.<type>.<static method>()
> 
> Why do you need both? If PostgreSQL is going to add new nice features 
> that enables better namespace handling and global variables, take a look 
> at Oracles UDT's with static and instance methods. Only thing that I'm 
> not sure is there is static variables. If it's missing, we could add 
> that easilly and give them the same life-span as the session.

It's been a while since I used types, but here's some issues I can think
of:

I don't believe types allow for internal-only methods. I seem to recall
other limitations on what types could do as opposed to packages. Of
course, we need not restrict ourselves in such a manner.

Types are not used nearly as much as packages (this is an issue if we
care about enabling Oracle users to migrate).

Types generally force you to use them in relation to some database
object. Packages have no such restriction.

Don't get me wrong, I think supporting more powerful types would be a
welcome addition, but I don't think they can be as flexable as packages.
The good news is that they should both be able to use the same
underlying framework. Types are afterall just a specialized
implementation of packages.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Oracle Style packages on postgres

decibel <decibel@decibel.org> — 2005-05-10T18:07:01Z

On Mon, May 09, 2005 at 09:56:53PM -0400, Bruce Momjian wrote:
> OK, so it seems we need:
> 
> 	C static/private functions for schemas
> 	C static/private variables for schemas
> 
> Are private variables implemented via the temporary per-session schema?
> 
> 	nested schemas
> 
> What does the standard say?
> 
> Is that it?

I think a big part of the usefulness of packages is in supplying an
additional level of grouping common things together. Of course, nested
schemas with public/private functions (and procedures, lest we forget
them) is a much better way to do this, since a schema can encompass
everything you'd need; tables, views, types, etc.

Having said that, I would say that private variables need to be exposed
via the same nested schema interface as everything else. If the
implementation under the covers is via the temporary schema, that's
fine.

As for using temporary tables as session storage, that has a huge
performance penalty associated with it. Part of the advantage to package
variables is that you can use them to cache information your code will
need to access frequently. That access then becomes a simple variable or
array read, which is obviously much faster than parsing a query to hit a
temp table.

There is one feature not mentioned by Bruce's design, and that's
initialization (and teardown) code. I don't recall using that capability
in Oracle, but I was wondering if others with more experience could
comment on it.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Oracle Style packages on postgres

Thomas Hallgren <thhal@mailblocks.com> — 2005-05-10T18:40:16Z

Jim C. Nasby wrote:

>I don't believe types allow for internal-only methods. I seem to recall
>other limitations on what types could do as opposed to packages. Of
>course, we need not restrict ourselves in such a manner.
>  
>
Do Oracle packages support internal only functions? If they do, then I 
agree, that's a feature that the SQL standard doesn't have.

>Types are not used nearly as much as packages (this is an issue if we
>care about enabling Oracle users to migrate).
>
>Types generally force you to use them in relation to some database
>object. Packages have no such restriction.
>  
>
If used as a package, i.e. only containing static methods, you don't 
need to use the type in relation to anything. It's simply a namespace. 
If used with a temporary table, you get a very neat, standardized, 
cross-language way of managing session data.

>Don't get me wrong, I think supporting more powerful types would be a
>welcome addition, but I don't think they can be as flexable as packages.
>  
>
I see this differently. A full implementation of the SQL-standard for 
UDT's will allow abstract types, inheritance, method overloading, etc. 
It quickly becomes far more flexible then Oracle packages. A full 
implementation is of course beyond the scope for what's needed to 
accommodate the needs of those who use packages but a simple 
implementation is extendable within the scope of the standard.

>The good news is that they should both be able to use the same
>underlying framework. Types are afterall just a specialized
>implementation of packages.
>  
>
Right. Given a good implementation of types, packages would be easy to 
implement. The other way around would not be possible. A package is a 
very restricted type that contains static methods only. Possibly with 
the extension of some kind of method/attribute visibility.

So do we need "internal only" functions although they are not covered by 
the SQL-standard? If the answer is no, then IMO we should follow the 
standard and use types, not packages. If the answer is yes, then the  
SQL-standard is not enough. Should we then use packages or simply 
introduce the keyword PRIVATE on methods of a type? Personally, I'd go 
for the latter and then, if necessary, build packages on top of that in 
for the benefit of Oracle users who wants to migrate. A fully fledged 
type system will ease Oracle migration too since Oracle already has this.

Regards,
Thomas Hallgren

Re: Oracle Style packages on postgres

decibel <decibel@decibel.org> — 2005-05-10T18:51:18Z

On Tue, May 10, 2005 at 08:40:16PM +0200, Thomas Hallgren wrote:
> Jim C. Nasby wrote:
> 
> >I don't believe types allow for internal-only methods. I seem to recall
> >other limitations on what types could do as opposed to packages. Of
> >course, we need not restrict ourselves in such a manner.
> > 
> >
> Do Oracle packages support internal only functions? If they do, then I 
> agree, that's a feature that the SQL standard doesn't have.

AFAIK anything in an Oracle packages can be internal-only. You just
exclude it from the header.

> I see this differently. A full implementation of the SQL-standard for 
> UDT's will allow abstract types, inheritance, method overloading, etc. 
> It quickly becomes far more flexible then Oracle packages. A full 
> implementation is of course beyond the scope for what's needed to 
> accommodate the needs of those who use packages but a simple 
> implementation is extendable within the scope of the standard.

Packages allow for abstract types and method overloading. They don't
allow for inheritance.

> >The good news is that they should both be able to use the same
> >underlying framework. Types are afterall just a specialized
> >implementation of packages.
> > 
> >
> Right. Given a good implementation of types, packages would be easy to 
> implement. The other way around would not be possible. A package is a 
> very restricted type that contains static methods only. Possibly with 
> the extension of some kind of method/attribute visibility.

I guess maybe I'm not clear on what you mean by static methods. IIRC, in
Oracle nomenclature, static means it will retain state between
invocations in the same session. Of course, functions and procedures
that don't do this are also allowed.

Basically, before we assume that one implementation allows for the other
I think some research needs to be done. Hopefully someone on the list is
familiar with both. I think it would be a huge win if we could offer a
compatability mechanism that makes it easy for Oracle packages to be
used in PostgreSQL, making migration from Oracle much, much easier.

> So do we need "internal only" functions although they are not covered by 
> the SQL-standard? If the answer is no, then IMO we should follow the 
> standard and use types, not packages. If the answer is yes, then the  
> SQL-standard is not enough. Should we then use packages or simply 
> introduce the keyword PRIVATE on methods of a type? Personally, I'd go 
> for the latter and then, if necessary, build packages on top of that in 
> for the benefit of Oracle users who wants to migrate. A fully fledged 
> type system will ease Oracle migration too since Oracle already has this.

I think both should allow for private functions/procedures/methods. BTW,
I'm also very keen on the idea of nested schemas, which is another
possible means to the package ends.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Oracle Style packages on postgres

Thomas Hallgren <thhal@mailblocks.com> — 2005-05-10T19:19:49Z

Jim C. Nasby wrote:

>I guess maybe I'm not clear on what you mean by static methods. IIRC, in
>Oracle nomenclature, static means it will retain state between
>invocations in the same session. Of course, functions and procedures
>that don't do this are also allowed.
>  
>
A STATIC prefix on a method simply means that it is not tied to a 
particular instance of the type where it is defined.

You have the type Foo with the method bar(). If the method is STATIC, 
you can use:

SELECT Foo.bar();

If it's an INSTANCE method, you can only call it when you have an 
instance available, so if FooTable is a table described by the type Foo 
and bar is non-static, you could write:

SELECT x.bar() FROM FooTable x;

>I think both should allow for private functions/procedures/methods. BTW,
>I'm also very keen on the idea of nested schemas, which is another
>possible means to the package ends.
>  
>
I'd like that too although I don't think it's included in the SQL-standard.

Regards,
Thomas Hallgren