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

   1 <!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.39 2008/03/28 00:21:55 tgl 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</> might 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, its arguments are passed as elements of the array
  65    <varname>args[]</varname>; named arguments are also passed as ordinary
  66    variables to the Python script. The result is returned from the Python code
  67    in the usual way, with <literal>return</literal> or
  68    <literal>yield</literal> (in case of a result-set 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
 105    value. Alternatively, one can use named parameters as shown in the example
 106    above.  Use of named parameters is usually more readable.
 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 &gt; 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    function. The following examples assume we have:
 165
 166 <programlisting>
 167 CREATE TYPE named_value AS (
 168   name   text,
 169   value  integer
 170 );
 171 </programlisting>
 172
 173    A composite result can be returned as a:
 174
 175    <variablelist>
 176     <varlistentry>
 177      <term>Sequence type (a tuple or list, but not a set because
 178      it is not indexable)</term>
 179      <listitem>
 180       <para>
 181        Returned sequence objects must have the same number of items as the
 182        composite result type has fields. The item with index 0 is assigned to
 183        the first field of the composite type, 1 to the second and so on. For
 184        example:
 185
 186 <programlisting>
 187 CREATE FUNCTION make_pair (name text, value integer)
 188   RETURNS named_value
 189 AS $$
 190   return [ name, value ]
 191   # or alternatively, as tuple: return ( name, value )
 192 $$ LANGUAGE plpythonu;
 193 </programlisting>
 194
 195        To return a SQL null for any column, insert <symbol>None</symbol> at
 196        the corresponding position.
 197       </para>
 198      </listitem>
 199     </varlistentry>
 200
 201     <varlistentry>
 202      <term>Mapping (dictionary)</term>
 203      <listitem>
 204       <para>
 205        The value for each result type column is retrieved from the mapping
 206        with the column name as key. Example:
 207
 208 <programlisting>
 209 CREATE FUNCTION make_pair (name text, value integer)
 210   RETURNS named_value
 211 AS $$
 212   return { "name": name, "value": value }
 213 $$ LANGUAGE plpythonu;
 214 </programlisting>
 215
 216        Any extra dictionary key/value pairs are ignored. Missing keys are
 217        treated as errors.
 218        To return a SQL null value for any column, insert
 219        <symbol>None</symbol> with the corresponding column name as the key.
 220       </para>
 221      </listitem>
 222     </varlistentry>
 223
 224     <varlistentry>
 225      <term>Object (any object providing method <literal>__getattr__</literal>)</term>
 226      <listitem>
 227       <para>
 228        This works the same as a mapping.
 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 value.
 258   </para>
 259
 260   <para>
 261    A <application>PL/Python</application> function can also return sets of
 262    scalar or composite types. There are several ways to achieve this because
 263    the returned object is internally turned into an iterator. The following
 264    examples assume we have composite type:
 265
 266 <programlisting>
 267 CREATE TYPE greeting AS (
 268   how text,
 269   who text
 270 );
 271 </programlisting>
 272
 273    A set result can be returned from a:
 274
 275    <variablelist>
 276     <varlistentry>
 277      <term>Sequence type (tuple, list, set)</term>
 278      <listitem>
 279       <para>
 280 <programlisting>
 281 CREATE FUNCTION greet (how text)
 282   RETURNS SETOF greeting
 283 AS $$
 284   # return tuple containing lists as composite types
 285   # all other combinations work also
 286   return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
 287 $$ LANGUAGE plpythonu;
 288 </programlisting>
 289       </para>
 290      </listitem>
 291     </varlistentry>
 292
 293     <varlistentry>
 294      <term>Iterator (any object providing <symbol>__iter__</symbol> and
 295       <symbol>next</symbol> methods)</term>
 296      <listitem>
 297       <para>
 298 <programlisting>
 299 CREATE FUNCTION greet (how text)
 300   RETURNS SETOF greeting
 301 AS $$
 302   class producer:
 303     def __init__ (self, how, who):
 304       self.how = how
 305       self.who = who
 306       self.ndx = -1
 307
 308     def __iter__ (self):
 309       return self
 310
 311     def next (self):
 312       self.ndx += 1
 313       if self.ndx == len(self.who):
 314         raise StopIteration
 315       return ( self.how, self.who[self.ndx] )
 316
 317   return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
 318 $$ LANGUAGE plpythonu;
 319 </programlisting>
 320       </para>
 321      </listitem>
 322     </varlistentry>
 323
 324     <varlistentry>
 325      <term>Generator (<literal>yield</literal>)</term>
 326      <listitem>
 327       <para>
 328 <programlisting>
 329 CREATE FUNCTION greet (how text)
 330   RETURNS SETOF greeting
 331 AS $$
 332   for who in [ "World", "PostgreSQL", "PL/Python" ]:
 333     yield ( how, who )
 334 $$ LANGUAGE plpythonu;
 335 </programlisting>
 336
 337        <warning>
 338         <para>
 339          Currently, due to Python
 340          <ulink url="http://sourceforge.net/tracker/index.php?func=detail&amp;aid=1483133&amp;group_id=5470&amp;atid=105470">bug #1483133</ulink>,
 341          some debug versions of Python 2.4
 342          (configured and compiled with option <literal>--with-pydebug</literal>)
 343          are known to crash the <productname>PostgreSQL</productname> server
 344          when using an iterator to return a set result.
 345          Unpatched versions of Fedora 4 contain this bug.
 346          It does not happen in production versions of Python or on patched
 347          versions of Fedora 4.
 348         </para>
 349        </warning>
 350       </para>
 351      </listitem>
 352     </varlistentry>
 353    </variablelist>
 354   </para>
 355
 356   <para>
 357    The global dictionary <varname>SD</varname> is available to store
 358    data between function calls.  This variable is private static data.
 359    The global dictionary <varname>GD</varname> is public data,
 360    available to all Python functions within a session.  Use with
 361    care.<indexterm><primary>global data</><secondary>in
 362    PL/Python</></indexterm>
 363   </para>
 364
 365   <para>
 366    Each function gets its own execution environment in the
 367    Python interpreter, so that global data and function arguments from
 368    <function>myfunc</function> are not available to
 369    <function>myfunc2</function>.  The exception is the data in the
 370    <varname>GD</varname> dictionary, as mentioned above.
 371   </para>
 372  </sect1>
 373
 374  <sect1 id="plpython-trigger">
 375   <title>Trigger Functions</title>
 376
 377   <indexterm zone="plpython-trigger">
 378    <primary>trigger</primary>
 379    <secondary>in PL/Python</secondary>
 380   </indexterm>
 381
 382   <para>
 383    When a function is used as a trigger, the dictionary
 384    <literal>TD</literal> contains trigger-related values.
 385    <literal>TD["event"]</> contains
 386    the event as a string (<literal>INSERT</>, <literal>UPDATE</>,
 387    <literal>DELETE</>, <literal>TRUNCATE</>, or <literal>UNKNOWN</>).
 388    <literal>TD["when"]</> contains one of <literal>BEFORE</>,
 389    <literal>AFTER</>, or <literal>UNKNOWN</>.
 390    <literal>TD["level"]</> contains one of <literal>ROW</>,
 391    <literal>STATEMENT</>, or <literal>UNKNOWN</>.
 392    For a row-level trigger, the trigger
 393    rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
 394    depending on the trigger event.
 395    <literal>TD["name"]</> contains the trigger name,
 396    <literal>TD["table_name"]</> contains the name of the table on which the trigger occurred,
 397    <literal>TD["table_schema"]</> contains the schema of the table on which the trigger occurred,
 398    and <literal>TD["relid"]</> contains the OID of the table on
 399    which the trigger occurred.  If the <command>CREATE TRIGGER</> command
 400    included arguments, they are available in <literal>TD["args"][0]</> to
 401    <literal>TD["args"][<replaceable>n</>-1]</>.
 402   </para>
 403
 404   <para>
 405    If <literal>TD["when"]</literal> is <literal>BEFORE</> and
 406    <literal>TD["level"]</literal> is <literal>ROW</>, you can
 407    return <literal>None</literal> or <literal>"OK"</literal> from the
 408    Python function to indicate the row is unmodified,
 409    <literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
 410    indicate you've modified the row.
 411    Otherwise the return value is ignored.
 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>