From 819f22a3022e246465361bb983070f3c92690b12 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sat, 2 Sep 2006 12:30:01 +0000 Subject: Allow PL/python to return composite types and result sets Sven Suursoho --- doc/src/sgml/plpython.sgml | 304 ++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 287 insertions(+), 17 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index ccccf66155d..a0cce540c54 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -1,4 +1,4 @@ - + PL/Python - Python Procedural Language @@ -46,28 +46,211 @@ PL/Python Functions - Functions in PL/Python are declared via the usual - syntax. For example: + 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 myfunc(text) RETURNS text - AS 'return args[0]' - LANGUAGE plpythonu; +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 - gets transformed into a Python function. - For example, the above results in + is transformed into a Python function. For example, the above results in -def __plpython_procedure_myfunc_23456(): - return args[0] +def __plpython_procedure_pymax_23456(): + if a > b: + return a + return b assuming that 23456 is the OID assigned to the function by PostgreSQL. + + 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 @@ -77,13 +260,100 @@ def __plpython_procedure_myfunc_23456(): - 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. + 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. -- cgit v1.2.3