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

   1 <!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.38 2007/02/01 00:28:17 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</> 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.  The trigger
 385    rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
 386    depending on the trigger event.  <literal>TD["event"]</> contains
 387    the event as a string (<literal>INSERT</>, <literal>UPDATE</>,
 388    <literal>DELETE</>, or <literal>UNKNOWN</>).
 389    <literal>TD["when"]</> contains one of <literal>BEFORE</>,
 390    <literal>AFTER</>, and <literal>UNKNOWN</>.
 391    <literal>TD["level"]</> contains one of <literal>ROW</>,
 392    <literal>STATEMENT</>, and <literal>UNKNOWN</>.
 393    <literal>TD["name"]</> contains the trigger name,
 394    <literal>TD["table_name"]</> contains the name of the table on which the trigger occurred,
 395    <literal>TD["table_schema"]</> contains the schema of the table on which the trigger occurred,
 396    <literal>TD["name"]</> contains the trigger name, and
 397    <literal>TD["relid"]</> contains the OID of the table on
 398    which the trigger occurred.  If the <command>CREATE TRIGGER</> command
 399    included arguments, they are available in <literal>TD["args"][0]</> to
 400    <literal>TD["args"][(<replaceable>n</>-1)]</>.
 401   </para>
 402
 403   <para>
 404    If <literal>TD["when"]</literal> is <literal>BEFORE</>, you can
 405    return <literal>None</literal> or <literal>"OK"</literal> from the
 406    Python function to indicate the row is unmodified,
 407    <literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
 408    indicate you've modified the row.
 409   </para>
 410  </sect1>
 411
 412  <sect1 id="plpython-database">
 413   <title>Database Access</title>
 414
 415   <para>
 416    The PL/Python language module automatically imports a Python module
 417    called <literal>plpy</literal>.  The functions and constants in
 418    this module are available to you in the Python code as
 419    <literal>plpy.<replaceable>foo</replaceable></literal>.  At present
 420    <literal>plpy</literal> implements the functions
 421    <literal>plpy.debug(<replaceable>msg</>)</literal>,
 422    <literal>plpy.log(<replaceable>msg</>)</literal>,
 423    <literal>plpy.info(<replaceable>msg</>)</literal>,
 424    <literal>plpy.notice(<replaceable>msg</>)</literal>,
 425    <literal>plpy.warning(<replaceable>msg</>)</literal>,
 426    <literal>plpy.error(<replaceable>msg</>)</literal>, and
 427    <literal>plpy.fatal(<replaceable>msg</>)</literal>.<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
 428    <function>plpy.error</function> and
 429    <function>plpy.fatal</function> actually raise a Python exception
 430    which, if uncaught, propagates out to the calling query, causing
 431    the current transaction or subtransaction to be aborted.
 432    <literal>raise plpy.ERROR(<replaceable>msg</>)</literal> and
 433    <literal>raise plpy.FATAL(<replaceable>msg</>)</literal> are
 434    equivalent to calling
 435    <function>plpy.error</function> and
 436    <function>plpy.fatal</function>, respectively.
 437    The other functions only generate messages of different
 438    priority levels.
 439    Whether messages of a particular priority are reported to the client,
 440    written to the server log, or both is controlled by the
 441    <xref linkend="guc-log-min-messages"> and
 442    <xref linkend="guc-client-min-messages"> configuration
 443    variables. See <xref linkend="runtime-config"> for more information.
 444   </para>
 445
 446   <para>
 447    Additionally, the <literal>plpy</literal> module provides two
 448    functions called <function>execute</function> and
 449    <function>prepare</function>.  Calling
 450    <function>plpy.execute</function> with a query string and an
 451    optional limit argument causes that query to be run and the result
 452    to be returned in a result object.  The result object emulates a
 453    list or dictionary object.  The result object can be accessed by
 454    row number and column name.  It has these additional methods:
 455    <function>nrows</function> which returns the number of rows
 456    returned by the query, and <function>status</function> which is the
 457    <function>SPI_execute()</function> return value.  The result object
 458    can be modified.
 459   </para>
 460
 461   <para>
 462    For example:
 463 <programlisting>
 464 rv = plpy.execute("SELECT * FROM my_table", 5)
 465 </programlisting>
 466    returns up to 5 rows from <literal>my_table</literal>.  If
 467    <literal>my_table</literal> has a column
 468    <literal>my_column</literal>, it would be accessed as:
 469 <programlisting>
 470 foo = rv[i]["my_column"]
 471 </programlisting>
 472   </para>
 473
 474   <para>
 475    <indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
 476    The second function, <function>plpy.prepare</function>, prepares
 477    the execution plan for a query.  It is called with a query string
 478    and a list of parameter types, if you have parameter references in
 479    the query.  For example:
 480 <programlisting>
 481 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
 482 </programlisting>
 483    <literal>text</literal> is the type of the variable you will be
 484    passing for <literal>$1</literal>.  After preparing a statement, you
 485    use the function <function>plpy.execute</function> to run it:
 486 <programlisting>
 487 rv = plpy.execute(plan, [ "name" ], 5)
 488 </programlisting>
 489    The third argument is the limit and is optional.
 490   </para>
 491
 492   <para>
 493    When you prepare a plan using the PL/Python module it is
 494    automatically saved.  Read the SPI documentation (<xref
 495    linkend="spi">) for a description of what this means.
 496    In order to make effective use of this across function calls
 497    one needs to use one of the persistent storage dictionaries
 498    <literal>SD</literal> or <literal>GD</literal> (see
 499    <xref linkend="plpython-funcs">). For example:
 500 <programlisting>
 501 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
 502     if SD.has_key("plan"):
 503         plan = SD["plan"]
 504     else:
 505         plan = plpy.prepare("SELECT 1")
 506         SD["plan"] = plan
 507     # rest of function
 508 $$ LANGUAGE plpythonu;
 509 </programlisting>
 510   </para>
 511  </sect1>
 512
 513 <![IGNORE[
 514  <!-- NOT CURRENTLY SUPPORTED -->
 515
 516  <sect1 id="plpython-trusted">
 517   <title>Restricted Environment</title>
 518
 519   <para>
 520    The current version of <application>PL/Python</application>
 521    functions as a trusted language only; access to the file system and
 522    other local resources is disabled.  Specifically,
 523    <application>PL/Python</application> uses the Python restricted
 524    execution environment, further restricts it to prevent the use of
 525    the file <function>open</> call, and allows only modules from a
 526    specific list to be imported.  Presently, that list includes:
 527    <literal>array</>, <literal>bisect</>, <literal>binascii</>,
 528    <literal>calendar</>, <literal>cmath</>, <literal>codecs</>,
 529    <literal>errno</>, <literal>marshal</>, <literal>math</>, <literal>md5</>,
 530    <literal>mpz</>, <literal>operator</>, <literal>pcre</>,
 531    <literal>pickle</>, <literal>random</>, <literal>re</>, <literal>regex</>,
 532    <literal>sre</>, <literal>sha</>, <literal>string</>, <literal>StringIO</>,
 533    <literal>struct</>, <literal>time</>, <literal>whrandom</>, and
 534    <literal>zlib</>.
 535   </para>
 536  </sect1>
 537
 538 ]]>
 539
 540 </chapter>