Re: plpython improvements

Sven Suursoho wrote:
> Hi,
> 
> 
> Quoting Bruce Momjian :
> 
> > Great.  Please supply documentation and it will be applied.  Thanks.
> 
> Here it comes, including src+doc patches.
> Updated sources according to Michael Fuhr's comments and fixed one FIXME.
> 
> Please check documentation patch thoroughly as I'm not native English
> speaker nor didn't manage to generate documentation from SGML sources (
> openjade:postgres.sgml:3:55:W: cannot generate system identifier for public
> text "-//OASIS//DTD DocBook V4.2//EN")

Patch applied.  Thanks.  I improved your documentation wording, updated
version attached.

-- 
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/plpython.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/plpython.sgml,v
retrieving revision 1.30
diff -c -c -r1.30 plpython.sgml
*** doc/src/sgml/plpython.sgml	26 May 2006 19:23:09 -0000	1.30
--- doc/src/sgml/plpython.sgml	2 Sep 2006 12:12:40 -0000
***************
*** 46,67 ****
    PL/Python Functions
  
    
!    Functions in PL/Python are declared via the usual 
!    syntax. For example:
  
! CREATE FUNCTION myfunc(text) RETURNS text
!     AS 'return args[0]'
!     LANGUAGE plpythonu;
  
  
     The Python code that is given as the body of the function definition
!    gets transformed into a Python function.
!    For example, the above results in
  
  
! def __plpython_procedure_myfunc_23456():
!         return args[0]
  
  
     assuming that 23456 is the OID assigned to the function by
--- 46,95 ----
    PL/Python Functions
  
    
!    Functions in PL/Python are declared via the standard 
!    syntax:
! 
! 
! CREATE FUNCTION funcname (argument-list)
!   RETURNS return-type
! AS $$
!   # PL/Python function body
! $$ LANGUAGE plpythonu;
! 
!   
! 
!   
!    The body of a function is simply a Python script. When the function
!    is called, all unnamed arguments are passed as elements to the array
!    args[] and named arguments as ordinary variables to the
!    Python script. The result is returned from the Python code in the usual way,
!    with return or yield (in case of
!    a resultset statement).
!   
! 
!   
!    For example, a function to return the greater of two integers can be
!    defined as:
! 
  
! CREATE FUNCTION pymax (a integer, b integer)
!   RETURNS integer
! AS $$
!   if a > b:
!     return a
!   return b
! $$ LANGUAGE plpythonu;
  
  
     The Python code that is given as the body of the function definition
!    is transformed into a Python function. For example, the above results in
  
  
! def __plpython_procedure_pymax_23456():
!   if a > b:
!     return a
!   return b
  
  
     assuming that 23456 is the OID assigned to the function by
***************
*** 69,74 ****
--- 97,257 ----
    
  
    
+    The PostgreSQL</> function parameters are available in
+    the global args list.  In the
+    pymax example, args[0] contains
+    whatever was passed in as the first argument and
+    args[1] contains the second argument's value. Alternatively,
+    one can use named parameters as shown in the example above. This greatly simplifies
+    the reading and writing of PL/Python code.
+   
+ 
+   
+    If an SQL null valuenull valuePL/Python is passed to a
+    function, the argument value will appear as None in
+    Python. The above function definition will return the wrong answer for null
+    inputs. We could add STRICT to the function definition
+    to make PostgreSQL do something more reasonable:
+    if a null value is passed, the function will not be called at all,
+    but will just return a null result automatically. Alternatively,
+    we could check for null inputs in the function body:
+ 
+ 
+ CREATE FUNCTION pymax (a integer, b integer)
+   RETURNS integer
+ AS $$
+   if (a is None) or (b is None):
+     return None
+   if a > b:
+     return a
+   return b
+ $$ LANGUAGE plpythonu;
+ 
+ 
+    As shown above, to return an SQL null value from a PL/Python
+    function, return the value None. This can be done whether the
+    function is strict or not.
+   
+ 
+   
+    Composite-type arguments are passed to the function as Python mappings. The
+    element names of the mapping are the attribute names of the composite type.
+    If an attribute in the passed row has the null value, it has the value
+    None in the mapping. Here is an example:
+ 
+ 
+ CREATE TABLE employee (
+   name text,
+   salary integer,
+   age integer
+ );
+ 
+ CREATE FUNCTION overpaid (e employee)
+   RETURNS boolean
+ AS $$
+   if e["salary"] > 200000:
+     return True
+   if (e["age"] < 30) and (e["salary"] > 100000):
+     return True
+   return False
+ $$ LANGUAGE plpythonu;
+ 
+   
+ 
+   
+    There are multiple ways to return row or composite types from a Python
+    scripts. In following examples we assume to have:
+ 
+ 
+ CREATE TABLE named_value (
+   name   text,
+   value  integer
+ );
+ 
+    or
+ 
+ CREATE TYPE named_value AS (
+   name   text,
+   value  integer
+ );
+ 
+ 
+    
+     
+      Sequence types (tuple or list), but not set (because
+      it is not indexable)
+      
+       
+        Returned sequence objects must have the same number of items as
+        composite types have fields. Item with index 0 is assigned to the first field
+        of the composite type, 1 to second and so on. For example:
+ 
+ 
+ CREATE FUNCTION make_pair (name text, value integer)
+   RETURNS named_value
+ AS $$
+   return [ name, value ]
+   # or alternatively, as tuple: return ( name, value )
+ $$ LANGUAGE plpythonu;
+ 
+ 
+        To return SQL null in any column, insert None at
+        the corresponding position.
+       
+      
+ 
+     
+      Mapping (dictionary)
+      
+       
+        Value for a composite type's column is retrieved from the mapping with
+        the column name as key. Example:
+ 
+ 
+ CREATE FUNCTION make_pair (name text, value integer)
+   RETURNS named_value
+ AS $$
+   return { "name": name, "value": value }
+ $$ LANGUAGE plpythonu;
+ 
+ 
+        Additional dictionary key/value pairs are ignored. Missing keys are
+        treated as errors, i.e. to return an SQL null value for any column, insert
+        None with the corresponding column name as the key.
+       
+      
+ 
+     
+      Object (any object providing method __getattr__)
+      
+       
+        Example:
+ 
+ 
+ CREATE FUNCTION make_pair (name text, value integer)
+   RETURNS named_value
+ AS $$
+   class named_value:
+     def __init__ (self, n, v):
+       self.name = n
+       self.value = v
+   return named_value(name, value)
+ 
+   # or simply
+   class nv: pass
+   nv.name = name
+   nv.value = value
+   return nv
+ $$ LANGUAGE plpythonu;
+ 
+       
+      
+     
+    
+   
+ 
+   
     If you do not provide a return value, Python returns the default
     None. PL/Python translates
     Python's None into the SQL null
***************
*** 77,89 ****
    
  
    
!    The PostgreSQL</> function parameters are available in
!    the global args list.  In the
!    myfunc example, args[0]</> contains
!    whatever was passed in as the text argument.  For
!    myfunc2(text, integer), args[0]</>
!    would contain the text argument and
!    args[1] the integer argument.
    
  
    
--- 260,359 ----
    
  
    
!    A PL/Python function can also return sets of
!    scalar or composite types. There are serveral ways to achieve this because
!    the returned object is internally turned into an iterator. For following
!    examples, let's assume to have composite type:
! 
! 
! CREATE TYPE greeting AS (
!   how text,
!   who text
! );
! 
!    
!    Currently known iterable types are:
!    
!     
!      Sequence types (tuple, list, set)
!      
!       
! 
! CREATE FUNCTION greet (how text)
!   RETURNS SETOF greeting
! AS $$
!   # return tuple containing lists as composite types
!   # all other combinations work also
!   return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
! $$ LANGUAGE plpythonu;
! 
!       
!      
!     
! 
!     
!      Iterator (any object providing __iter__ and
!       next methods)
!      
!       
! 
! CREATE FUNCTION greet (how text)
!   RETURNS SETOF greeting
! AS $$
!   class producer:
!     def __init__ (self, how, who):
!       self.how = how
!       self.who = who
!       self.ndx = -1
! 
!     def __iter__ (self):
!       return self
! 
!     def next (self):
!       self.ndx += 1
!       if self.ndx == len(self.who):
!         raise StopIteration
!       return ( self.how, self.who[self.ndx] )
! 
!   return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
! $$ LANGUAGE plpythonu;
! 
!       
!      
!     
! 
!     
!      Generator (yield)
!      
!       
! 
! CREATE FUNCTION greet (how text)
!   RETURNS SETOF greeting
! AS $$
!   for who in [ "World", "PostgreSQL", "PL/Python" ]:
!     yield ( how, who )
! $$ LANGUAGE plpythonu;
! 
! 
!        
!         
!          Currently, due to Python 
!          bug #1483133,
!          some debug versions of Python 2.4
!          (configured and compiled with option --with-pydebug)
!          are known to crash the PostgreSQL server.
!          Unpatched versions of Fedora 4 contain this bug.
!          It does not happen in production version of Python or on patched
!          versions of Fedora 4.
!         
!        
!       
!      
!     
!    
! 
!    Whenever new iterable types are added to Python language,
!    PL/Python is ready to use it.