AnalyticDB:PL/Python functions in AnalyticDB for PostgreSQL V7.0

Usage notes

• By default, PL/Python supports plpython3u. In this topic, plpython3u is used to describe how to use PL/Python.

• We recommend that you do not use plpython3u to perform high-risk operations because plpython3u is considered untrusted. AnalyticDB for PostgreSQL imposes limits on plpython3u. To use plpython3u, submit a ticket.

• PL/Python triggers are not supported.

• The UPDATE ... WHERE CURRENT OF and DELETE ... WHERE CURRENT OF statements cannot be executed to implement updatable cursors.

Limits

Only AnalyticDB for PostgreSQL V7.0 supports PL/Python.

Syntax

You can use a standard declaration of the CREATE FUNCTION statement to create a user-defined function (UDF). For more information, see CREATE FUNCTION.

CREATE FUNCTION function_name ()
  RETURNS return_type
AS $$
  # PL/Python function_body
$$ LANGUAGE plpython3u STRICT;

Parameters

• function_name: the name of the UDF. This parameter is required.

• return_type: the data type of the return value. This parameter is required.

• function_body: the body of the UDF. This parameter is required. When you invoke the UDF, the function_body parameter is passed into the UDF in the format of args[] elements, and named parameters are passed into the Python script as ordinary variables. A result of the data type specified in the RETURNS clause is returned.

• STRICT: If an input argument is null, the UDF automatically returns null, instead of running the code specified in the function_body parameter. This eliminates unnecessary calculations and simplifies the code. This parameter is optional.

  In the following example, two SQL statements have the same effect to create a UDF that returns the larger of two values. If an input argument is null, null is returned. However, the left-side SQL statement that contains the STRICT parameter is simpler.

  CREATE FUNCTION py_int_max (a integer, b integer) RETURNS integer AS $$ RETURN MAX(a,b) $$ LANGUAGE plpython3u STRICT;

  CREATE FUNCTION py_int_max (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 plpython3u;

Data type mappings

Basic data types

The following table describes the data type mappings between AnalyticDB for PostgreSQL and Python. For more information, see Data Values.

Arrays and lists

SQL array values passed into a plpython3u function are automatically converted into a Python list. A Python list returned from a plpython3u function is automatically converted into SQL array values.

One-dimensional arrays

In most cases of plpython3u functions, you can use brackets ([]) to specify one-dimensional arrays.

In the following example, a plpython3u function that returns a one-dimensional array of integers is created and invoked:

CREATE FUNCTION create_py_int_array()
  RETURNS int[]
AS $$
  RETURN [1, 2, 3, 4]
$$ LANGUAGE plpython3u;

Invoke the function.

SELECT create_py_int_array();

The following result is returned:

create_py_int_array
---------------------
{1,2,3,4}
(1 row) 

Multidimensional arrays

In plpython3u functions, a multidimensional array is considered a list of lists. Multidimensional arrays are passed into plpython3u functions as nested Python lists. When a plpython3u function returns a multidimensional array, the inner lists at each level must be of the same size.

In the following example, a plpython3u function is created and invoked. The function takes a multidimensional array of integers as inputs and returns a multidimensional array.

CREATE FUNCTION create_multidim_py_array(x int4[]) 
  RETURNS int4[]
AS $$
  plpy.info(x, type(x))
  RETURN x
$$ LANGUAGE plpython3u;

Invoke the function.

SELECT * FROM create_multidim_py_array(ARRAY[[1,2,3], [4,5,6]]);

The following result is returned:

 create_multidim_py_array 
--------------------------
 {{1,2,3},{4,5,6}}
(1 row) 

Composite types

You can pass composite-type arguments into a plpython3u function as Python mappings. The element names of the mapping are the attribute names of the composite type. If the value of an attribute is null, the mapping value of the attribute is None. A composite-type result can be returned as a sequence type (tuple or list). When an array of composite types is returned, the array must be specified as tuples. You cannot return an array of composite types as a list because this results in ambiguous data types.

In the following example, a composite type and a plpython3u function that returns a composite-type array are created:

CREATE TYPE type_record AS (
  FIRST text,
  SECOND int4
);

CREATE FUNCTION composite_type_as_list()
  RETURNS type_record[]
AS $$              
  RETURN [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)]];
$$ LANGUAGE plpython3u;

Invoke the function.

SELECT * FROM composite_type_as_list();

The following result is returned:

                               composite_type_as_list                           
------------------------------------------------------------------------------------
 {{"(first,1)","(second,1)"},{"(first,2)","(second,2)"}}
(1 row) 

For more information about how plpython3u processes arrays and composite types, see Arrays, Lists.

Set-returning functions

A plpython3u function can return a set of scalars or composite types based on a sequence type (tuple, list, or set).

In the following example, a composite type and a plpython3u function that returns a composite-type set are created:

-- Create a composite type.
CREATE TYPE greeting AS (
  how text,
  who text
);

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  # RETURN tuple containing lists AS composite types
  # ALL other combinations work also
  RETURN ( {"how": how, "who": "World"}, {"how": how, "who": "ADB PG"} )
$$ LANGUAGE plpython3u;

Invoke the function.

SELECT greet('hello');

The following result is returned:

       greet
-------------------
 (hello,World)
 (hello,ADB PG)
(2 rows)

Prepare and execute SQL queries

In the plpython3u language, the plpy module provides the plpy.execute() and plpy.prepare() functions to execute SQL queries. The plpython3u language also uses the plpy.subtransaction() function to manage plpy.execute() invocations in explicit subtransactions. For more information about the plpy.subtransaction() function, see Explicit Subtransactions.

plpy.execute()

The plpy.execute() function can be used to execute SQL queries. The plpy.execute() function supports two parameters: the SQL statement (required) and the maximum number of returned rows (optional). The plpy.execute() function returns a Python result object. The result object can be accessed as a list or a dictionary. You can access the rows in the result object by using the row number and the column name. The row number starts from 0.

For example, you can invoke the plpy.execute() function to query the my_table table and return up to five rows of data. The returned results are stored in the rv object.

rv = plpy.execute("SELECT * FROM my_table", 5).

If the my_table table contains the my_column column, you can use the column name to access the rows in the rv object by using the following code: my_col_data = rv[i]["my_column"].

Result objects support additional methods, such as nrows() and status(). For more information, see Database Access.

plpy.prepare()

The plpy.prepare() function can be used to prepare the execution plan for a query. When you invoke the plpy.prepare() function, you must specify a query string. If the query string contains parameter references, you must specify a list of parameter types.

Invoke the following plpy.prepare() function to create an execution plan named plan:

# The text parameter specifies the data type of the $1 variable. 
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"]) 

Invoke the following plpy.execute() function to execute an execution plan:

rv = plpy.execute(plan, [ "Fred" ])

DO anonymous code block

You can execute the following DO statement to run an anonymous code block:

CREATE TEMP TABLE temp_tbl AS VALUES (2) DISTRIBUTED RANDOMLY;
DO $$
  row = plpy.execute("SELECT * FROM temp_tbl", 1)
  attr = row[0]["column1"]
  plpy.notice("attr is %s" % attr)
$$ LANGUAGE plpython3u;

Handle Python errors and messages

The Python module plpy provides the plpy.debug(), plpy.log(), plpy.info(), plpy.notice(), plpy.warning(), plpy.error(), and plpy.fatal() functions to manage errors and messages.

The plpy.error() and plpy.fatal() functions raise a Python exception. If the exception is not caught, the exception propagates out to the calling query, causing the current transaction or subtransaction to be canceled. The raise plpy.error(msg) and raise plpy.fatal(msg) functions are equivalent to plpy.error() and plpy.fatal(), respectively. The other message functions only generate messages of different priority levels.

Manage Python dependencies

Python dependencies are stored on the local SSDs of the coordinator and compute nodes. If you want to install other dependencies in the Alibaba Cloud image website, submit a ticket.