granicus.if.org Git - postgresql/blob - doc/src/sgml/plpython.sgml

   1 <!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.31 2006/09/02 12:30:01 momjian Exp $ -->
   2
   3 <chapter id="plpython">
   4  <title>PL/Python - Python Procedural Language</title>
   5
   6  <indexterm zone="plpython"><primary>PL/Python</></>
   7  <indexterm zone="plpython"><primary>Python</></>
   8
   9  <para>
  10   The <application>PL/Python</application> procedural language allows
  11   <productname>PostgreSQL</productname> functions to be written in the
  12   <ulink url="http://www.python.org">Python language</ulink>.
  13  </para>
  14
  15  <para>
  16   To install PL/Python in a particular database, use
  17   <literal>createlang plpythonu <replaceable>dbname</></literal>.
  18  </para>
  19
  20   <tip>
  21    <para>
  22     If a language is installed into <literal>template1</>, all subsequently
  23     created databases will have the language installed automatically.
  24    </para>
  25   </tip>
  26
  27  <para>
  28   As of <productname>PostgreSQL</productname> 7.4, PL/Python is only
  29   available as an <quote>untrusted</> language (meaning it does not
  30   offer any way of restricting what users can do in it).  It has
  31   therefore been renamed to <literal>plpythonu</>.  The trusted
  32   variant <literal>plpython</> may become available again in future,
  33   if a new secure execution mechanism is developed in Python.
  34  </para>
  35
  36  <note>
  37   <para>
  38    Users of source packages must specially enable the build of
  39    PL/Python during the installation process.  (Refer to the
  40    installation instructions for more information.)  Users of binary
  41    packages might find PL/Python in a separate subpackage.
  42   </para>
  43  </note>
  44
  45  <sect1 id="plpython-funcs">
  46   <title>PL/Python Functions</title>
  47
  48   <para>
  49    Functions in PL/Python are declared via the standard <xref
  50    linkend="sql-createfunction" endterm="sql-createfunction-title">
  51    syntax:
  52
  53 <programlisting>
  54 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-list</replaceable>)
  55   RETURNS <replaceable>return-type</replaceable>
  56 AS $$
  57   # PL/Python function body
  58 $$ LANGUAGE plpythonu;
  59 </programlisting>
  60   </para>
  61
  62   <para>
  63    The body of a function is simply a Python script. When the function
  64    is called, all unnamed arguments are passed as elements to the array
  65    <varname>args[]</varname> and named arguments as ordinary variables to the
  66    Python script. The result is returned from the Python code in the usual way,
  67    with <literal>return</literal> or <literal>yield</literal> (in case of
  68    a resultset statement).
  69   </para>
  70
  71   <para>
  72    For example, a function to return the greater of two integers can be
  73    defined as:
  74
  75 <programlisting>
  76 CREATE FUNCTION pymax (a integer, b integer)
  77   RETURNS integer
  78 AS $$
  79   if a &gt; b:
  80     return a
  81   return b
  82 $$ LANGUAGE plpythonu;
  83 </programlisting>
  84
  85    The Python code that is given as the body of the function definition
  86    is transformed into a Python function. For example, the above results in
  87
  88 <programlisting>
  89 def __plpython_procedure_pymax_23456():
  90   if a &gt; b:
  91     return a
  92   return b
  93 </programlisting>
  94
  95    assuming that 23456 is the OID assigned to the function by
  96    <productname>PostgreSQL</productname>.
  97   </para>
  98
  99   <para>
 100    The <productname>PostgreSQL</> function parameters are available in
 101    the global <varname>args</varname> list.  In the
 102    <function>pymax</function> example, <varname>args[0]</varname> contains
 103    whatever was passed in as the first argument and
 104    <varname>args[1]</varname> contains the second argument's value. Alternatively,
 105    one can use named parameters as shown in the example above. This greatly simplifies
 106    the reading and writing of <application>PL/Python</application> code.
 107   </para>
 108
 109   <para>
 110    If an SQL null value<indexterm><primary>null value</primary><secondary
 111    sortas="PL/Python">PL/Python</secondary></indexterm> is passed to a
 112    function, the argument value will appear as <symbol>None</symbol> in
 113    Python. The above function definition will return the wrong answer for null
 114    inputs. We could add <literal>STRICT</literal> to the function definition
 115    to make <productname>PostgreSQL</productname> do something more reasonable:
 116    if a null value is passed, the function will not be called at all,
 117    but will just return a null result automatically. Alternatively,
 118    we could check for null inputs in the function body:
 119
 120 <programlisting>
 121 CREATE FUNCTION pymax (a integer, b integer)
 122   RETURNS integer
 123 AS $$
 124   if (a is None) or (b is None):
 125     return None
 126   if a > b:
 127     return a
 128   return b
 129 $$ LANGUAGE plpythonu;
 130 </programlisting>
 131
 132    As shown above, to return an SQL null value from a PL/Python
 133    function, return the value <symbol>None</symbol>. This can be done whether the
 134    function is strict or not.
 135   </para>
 136
 137   <para>
 138    Composite-type arguments are passed to the function as Python mappings. The
 139    element names of the mapping are the attribute names of the composite type.
 140    If an attribute in the passed row has the null value, it has the value
 141    <symbol>None</symbol> in the mapping. Here is an example:
 142
 143 <programlisting>
 144 CREATE TABLE employee (
 145   name text,
 146   salary integer,
 147   age integer
 148 );
 149
 150 CREATE FUNCTION overpaid (e employee)
 151   RETURNS boolean
 152 AS $$
 153   if e["salary"] &gt; 200000:
 154     return True
 155   if (e["age"] &lt; 30) and (e["salary"] &gt; 100000):
 156     return True
 157   return False
 158 $$ LANGUAGE plpythonu;
 159 </programlisting>
 160   </para>
 161
 162   <para>
 163    There are multiple ways to return row or composite types from a Python
 164    scripts. In following examples we assume to have:
 165
 166 <programlisting>
 167 CREATE TABLE named_value (
 168   name   text,
 169   value  integer
 170 );
 171 </programlisting>
 172    or
 173 <programlisting>
 174 CREATE TYPE named_value AS (
 175   name   text,
 176   value  integer
 177 );
 178 </programlisting>
 179
 180    <variablelist>
 181     <varlistentry>
 182      <term>Sequence types (tuple or list), but not <literal>set</literal> (because
 183      it is not indexable)</term>
 184      <listitem>
 185       <para>
 186        Returned sequence objects must have the same number of items as
 187        composite types have fields. Item with index 0 is assigned to the first field
 188        of the composite type, 1 to second and so on. For example:
 189
 190 <programlisting>
 191 CREATE FUNCTION make_pair (name text, value integer)
 192   RETURNS named_value
 193 AS $$
 194   return [ name, value ]
 195   # or alternatively, as tuple: return ( name, value )
 196 $$ LANGUAGE plpythonu;
 197 </programlisting>
 198
 199        To return SQL null in any column, insert <symbol>None</symbol> at
 200        the corresponding position.
 201       </para>
 202      </listitem>
 203
 204     <varlistentry>
 205      <term>Mapping (dictionary)</term>
 206      <listitem>
 207       <para>
 208        Value for a composite type's column is retrieved from the mapping with
 209        the column name as key. Example:
 210
 211 <programlisting>
 212 CREATE FUNCTION make_pair (name text, value integer)
 213   RETURNS named_value
 214 AS $$
 215   return { "name": name, "value": value }
 216 $$ LANGUAGE plpythonu;
 217 </programlisting>
 218
 219        Additional dictionary key/value pairs are ignored. Missing keys are
 220        treated as errors, i.e. to return an SQL null value for any column, insert
 221        <symbol>None</symbol> with the corresponding column name as the key.
 222       </para>
 223      </listitem>
 224
 225     <varlistentry>
 226      <term>Object (any object providing method <literal>__getattr__</literal>)</term>
 227      <listitem>
 228       <para>
 229        Example:
 230
 231 <programlisting>
 232 CREATE FUNCTION make_pair (name text, value integer)
 233   RETURNS named_value
 234 AS $$
 235   class named_value:
 236     def __init__ (self, n, v):
 237       self.name = n
 238       self.value = v
 239   return named_value(name, value)
 240
 241   # or simply
 242   class nv: pass
 243   nv.name = name
 244   nv.value = value
 245   return nv
 246 $$ LANGUAGE plpythonu;
 247 </programlisting>
 248       </para>
 249      </listitem>
 250     </varlistentry>
 251    </variablelist>
 252   </para>
 253
 254   <para>
 255    If you do not provide a return value, Python returns the default
 256    <symbol>None</symbol>. <application>PL/Python</application> translates
 257    Python's <symbol>None</symbol> into the SQL null
 258    value.<indexterm><primary>null value</><secondary
 259    sortas="PL/Python">in PL/Python</></indexterm>
 260   </para>
 261
 262   <para>
 263    A <application>PL/Python</application> function can also return sets of
 264    scalar or composite types. There are serveral ways to achieve this because
 265    the returned object is internally turned into an iterator. For following
 266    examples, let's assume to have composite type:
 267
 268 <programlisting>
 269 CREATE TYPE greeting AS (
 270   how text,
 271   who text
 272 );
 273 </programlisting>
 274
 275    Currently known iterable types are:
 276    <variablelist>
 277     <varlistentry>
 278      <term>Sequence types (tuple, list, set)</term>
 279      <listitem>
 280       <para>
 281 <programlisting>
 282 CREATE FUNCTION greet (how text)
 283   RETURNS SETOF greeting
 284 AS $$
 285   # return tuple containing lists as composite types
 286   # all other combinations work also
 287   return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
 288 $$ LANGUAGE plpythonu;
 289 </programlisting>
 290       </para>
 291      </listitem>
 292     </varlistentry>
 293
 294     <varlistentry>
 295      <term>Iterator (any object providing <symbol>__iter__</symbol> and
 296       <symbol>next</symbol> methods)</term>
 297      <listitem>
 298       <para>
 299 <programlisting>
 300 CREATE FUNCTION greet (how text)
 301   RETURNS SETOF greeting
 302 AS $$
 303   class producer:
 304     def __init__ (self, how, who):
 305       self.how = how
 306       self.who = who
 307       self.ndx = -1
 308
 309     def __iter__ (self):
 310       return self
 311
 312     def next (self):
 313       self.ndx += 1
 314       if self.ndx == len(self.who):
 315         raise StopIteration
 316       return ( self.how, self.who[self.ndx] )
 317
 318   return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
 319 $$ LANGUAGE plpythonu;
 320 </programlisting>
 321       </para>
 322      </listitem>
 323     </varlistentry>
 324
 325     <varlistentry>
 326      <term>Generator (<literal>yield</literal>)</term>
 327      <listitem>
 328       <para>
 329 <programlisting>
 330 CREATE FUNCTION greet (how text)
 331   RETURNS SETOF greeting
 332 AS $$
 333   for who in [ "World", "PostgreSQL", "PL/Python" ]:
 334     yield ( how, who )
 335 $$ LANGUAGE plpythonu;
 336 </programlisting>
 337
 338        <warning>
 339         <para>
 340          Currently, due to Python
 341          <ulink url="http://sourceforge.net/tracker/index.php?func=detail&amp;aid=1483133&amp;group_id=5470&amp;atid=105470">bug #1483133</ulink>,
 342          some debug versions of Python 2.4
 343          (configured and compiled with option <literal>--with-pydebug</literal>)
 344          are known to crash the <productname>PostgreSQL</productname> server.
 345          Unpatched versions of Fedora 4 contain this bug.
 346          It does not happen in production version of Python or on patched
 347          versions of Fedora 4.
 348         </para>
 349        </warning>
 350       </para>
 351      </listitem>
 352     </varlistentry>
 353    </variablelist>
 354
 355    Whenever new iterable types are added to Python language,
 356    <application>PL/Python</application> is ready to use it.
 357   </para>
 358
 359   <para>
 360    The global dictionary <varname>SD</varname> is available to store
 361    data between function calls.  This variable is private static data.
 362    The global dictionary <varname>GD</varname> is public data,
 363    available to all Python functions within a session.  Use with
 364    care.<indexterm><primary>global data</><secondary>in
 365    PL/Python</></indexterm>
 366   </para>
 367
 368   <para>
 369    Each function gets its own execution environment in the
 370    Python interpreter, so that global data and function arguments from
 371    <function>myfunc</function> are not available to
 372    <function>myfunc2</function>.  The exception is the data in the
 373    <varname>GD</varname> dictionary, as mentioned above.
 374   </para>
 375  </sect1>
 376
 377  <sect1 id="plpython-trigger">
 378   <title>Trigger Functions</title>
 379
 380   <indexterm zone="plpython-trigger">
 381    <primary>trigger</primary>
 382    <secondary>in PL/Python</secondary>
 383   </indexterm>
 384
 385   <para>
 386    When a function is used as a trigger, the dictionary
 387    <literal>TD</literal> contains trigger-related values.  The trigger
 388    rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
 389    depending on the trigger event.  <literal>TD["event"]</> contains
 390    the event as a string (<literal>INSERT</>, <literal>UPDATE</>,
 391    <literal>DELETE</>, or <literal>UNKNOWN</>).
 392    <literal>TD["when"]</> contains one of <literal>BEFORE</>,
 393    <literal>AFTER</>, and <literal>UNKNOWN</>.
 394    <literal>TD["level"]</> contains one of <literal>ROW</>,
 395    <literal>STATEMENT</>, and <literal>UNKNOWN</>.
 396    <literal>TD["name"]</> contains the trigger name,
 397    <literal>TD["table_name"]</> contains the name of the table on which the trigger occurred,
 398    <literal>TD["table_schema"]</> contains the schema of the table on which the trigger occurred,
 399    <literal>TD["name"]</> contains the trigger name, and
 400    <literal>TD["relid"]</> contains the OID of the table on
 401    which the trigger occurred.  If the <command>CREATE TRIGGER</> command
 402    included arguments, they are available in <literal>TD["args"][0]</> to
 403    <literal>TD["args"][(<replaceable>n</>-1)]</>.
 404   </para>
 405
 406   <para>
 407    If <literal>TD["when"]</literal> is <literal>BEFORE</>, you may
 408    return <literal>None</literal> or <literal>"OK"</literal> from the
 409    Python function to indicate the row is unmodified,
 410    <literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
 411    indicate you've modified the row.
 412   </para>
 413  </sect1>
 414
 415  <sect1 id="plpython-database">
 416   <title>Database Access</title>
 417
 418   <para>
 419    The PL/Python language module automatically imports a Python module
 420    called <literal>plpy</literal>.  The functions and constants in
 421    this module are available to you in the Python code as
 422    <literal>plpy.<replaceable>foo</replaceable></literal>.  At present
 423    <literal>plpy</literal> implements the functions
 424    <literal>plpy.debug(<replaceable>msg</>)</literal>,
 425    <literal>plpy.log(<replaceable>msg</>)</literal>,
 426    <literal>plpy.info(<replaceable>msg</>)</literal>,
 427    <literal>plpy.notice(<replaceable>msg</>)</literal>,
 428    <literal>plpy.warning(<replaceable>msg</>)</literal>,
 429    <literal>plpy.error(<replaceable>msg</>)</literal>, and
 430    <literal>plpy.fatal(<replaceable>msg</>)</literal>.<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
 431    <function>plpy.error</function> and
 432    <function>plpy.fatal</function> actually raise a Python exception
 433    which, if uncaught, propagates out to the calling query, causing
 434    the current transaction or subtransaction to be aborted.
 435    <literal>raise plpy.ERROR(<replaceable>msg</>)</literal> and
 436    <literal>raise plpy.FATAL(<replaceable>msg</>)</literal> are
 437    equivalent to calling
 438    <function>plpy.error</function> and
 439    <function>plpy.fatal</function>, respectively.
 440    The other functions only generate messages of different
 441    priority levels.
 442    Whether messages of a particular priority are reported to the client,
 443    written to the server log, or both is controlled by the
 444    <xref linkend="guc-log-min-messages"> and
 445    <xref linkend="guc-client-min-messages"> configuration
 446    variables. See <xref linkend="runtime-config"> for more information.
 447   </para>
 448
 449   <para>
 450    Additionally, the <literal>plpy</literal> module provides two
 451    functions called <function>execute</function> and
 452    <function>prepare</function>.  Calling
 453    <function>plpy.execute</function> with a query string and an
 454    optional limit argument causes that query to be run and the result
 455    to be returned in a result object.  The result object emulates a
 456    list or dictionary object.  The result object can be accessed by
 457    row number and column name.  It has these additional methods:
 458    <function>nrows</function> which returns the number of rows
 459    returned by the query, and <function>status</function> which is the
 460    <function>SPI_execute()</function> return value.  The result object
 461    can be modified.
 462   </para>
 463
 464   <para>
 465    For example,
 466 <programlisting>
 467 rv = plpy.execute("SELECT * FROM my_table", 5)
 468 </programlisting>
 469    returns up to 5 rows from <literal>my_table</literal>.  If
 470    <literal>my_table</literal> has a column
 471    <literal>my_column</literal>, it would be accessed as
 472 <programlisting>
 473 foo = rv[i]["my_column"]
 474 </programlisting>
 475   </para>
 476
 477   <para>
 478    <indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
 479    The second function, <function>plpy.prepare</function>, prepares
 480    the execution plan for a query.  It is called with a query string
 481    and a list of parameter types, if you have parameter references in
 482    the query.  For example:
 483 <programlisting>
 484 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
 485 </programlisting>
 486    <literal>text</literal> is the type of the variable you will be
 487    passing for <literal>$1</literal>.  After preparing a statement, you
 488    use the function <function>plpy.execute</function> to run it:
 489 <programlisting>
 490 rv = plpy.execute(plan, [ "name" ], 5)
 491 </programlisting>
 492    The third argument is the limit and is optional.
 493   </para>
 494
 495   <para>
 496    When you prepare a plan using the PL/Python module it is
 497    automatically saved.  Read the SPI documentation (<xref
 498    linkend="spi">) for a description of what this means.
 499    In order to make effective use of this across function calls
 500    one needs to use one of the persistent storage dictionaries
 501    <literal>SD</literal> or <literal>GD</literal> (see
 502    <xref linkend="plpython-funcs">). For example:
 503 <programlisting>
 504 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
 505     if SD.has_key("plan"):
 506         plan = SD["plan"]
 507     else:
 508         plan = plpy.prepare("SELECT 1")
 509         SD["plan"] = plan
 510     # rest of function
 511 $$ LANGUAGE plpythonu;
 512 </programlisting>
 513   </para>
 514  </sect1>
 515
 516 <![IGNORE[
 517  <!-- NOT CURRENTLY SUPPORTED -->
 518
 519  <sect1 id="plpython-trusted">
 520   <title>Restricted Environment</title>
 521
 522   <para>
 523    The current version of <application>PL/Python</application>
 524    functions as a trusted language only; access to the file system and
 525    other local resources is disabled.  Specifically,
 526    <application>PL/Python</application> uses the Python restricted
 527    execution environment, further restricts it to prevent the use of
 528    the file <function>open</> call, and allows only modules from a
 529    specific list to be imported.  Presently, that list includes:
 530    <literal>array</>, <literal>bisect</>, <literal>binascii</>,
 531    <literal>calendar</>, <literal>cmath</>, <literal>codecs</>,
 532    <literal>errno</>, <literal>marshal</>, <literal>math</>, <literal>md5</>,
 533    <literal>mpz</>, <literal>operator</>, <literal>pcre</>,
 534    <literal>pickle</>, <literal>random</>, <literal>re</>, <literal>regex</>,
 535    <literal>sre</>, <literal>sha</>, <literal>string</>, <literal>StringIO</>,
 536    <literal>struct</>, <literal>time</>, <literal>whrandom</>, and
 537    <literal>zlib</>.
 538   </para>
 539  </sect1>
 540
 541 ]]>
 542
 543 </chapter>