PL/Python user-defined functions-AnalyticDB(AnalyticDB)-阿里云帮助中心

Usage notes

plpython3u is considered untrusted. Avoid using it for high-risk operations. To enable plpython3u, submit a ticketsubmit a ticket.
PL/Python triggers are not supported.
UPDATE ... WHERE CURRENT OF and DELETE ... WHERE CURRENT OF cannot be used for updatable cursors.

Syntax

Use the standard CREATE FUNCTION statement to create a UDF. For the full syntax reference, see CREATE FUNCTION.

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

Parameters

Parameter	Description	Required
`function_name`	Name of the UDF.	Yes
`return_type`	Data type of the return value.	Yes
`function_body`	Body of the UDF. Arguments are available as `args[]` elements; named parameters are passed as ordinary Python variables.	Yes
`STRICT`	If any input argument is `NULL`, the function returns `NULL` immediately without executing the body. Simplifies null handling.	No

The STRICT keyword

The following two functions have identical behavior: they return the larger of two integers and return NULL if either input is NULL. The version with STRICT is shorter because null checking is handled automatically.

-- With STRICT: null handling is automatic
CREATE FUNCTION py_int_max(a integer, b integer)
  RETURNS integer
AS $$
  return max(a, b)
$$ LANGUAGE plpython3u STRICT;

-- Without STRICT: null handling must be written explicitly
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

When a value is passed into a plpython3u function, it is converted from its AnalyticDB for PostgreSQL type to the corresponding Python type. The following table lists the mappings.

AnalyticDB for PostgreSQL type	Python type
BOOLEAN	bool
BYTEA	bytes
SMALLINT, INT, BIGINT, or OID	int
REAL or DOUBLE	float
NUMERIC	Decimal

Arrays and lists

SQL arrays passed into a plpython3u function are automatically converted to Python lists. Python lists returned from a plpython3u function are automatically converted back to SQL arrays.

One-dimensional arrays

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

SELECT create_py_int_array();

Result:

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

Multidimensional arrays

Multidimensional arrays are represented as nested Python lists. When returning a multidimensional array, all inner lists at each level must be the same size.

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

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

Result:

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

Composite types

Composite-type arguments are passed into a plpython3u function as Python mappings, where each key is an attribute name and NULL attribute values map to None.

Return composite types as a sequence type (tuple or list). When returning an array of composite types, use tuples — returning a list of composite types causes ambiguous data types.

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;

SELECT * FROM composite_type_as_list();

Result:

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

For more information, see Arrays, Lists.

Set-returning functions

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

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

CREATE FUNCTION greet(how text)
  RETURNS SETOF greeting
AS $$
  return ({"how": how, "who": "World"}, {"how": how, "who": "ADB PG"})
$$ LANGUAGE plpython3u;

SELECT greet('hello');

Result:

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

Execute SQL queries

The plpy module provides functions to run SQL queries from within a PL/Python function. The plpython3u language also uses the plpy.subtransaction() function to manage plpy.execute() invocations in explicit subtransactions. For more information, see Explicit Subtransactions.

plpy.execute()

plpy.execute(query[, max_rows]) runs a SQL query and returns a result object. Access rows by index (starting from 0) and by column name.

# Query my_table and return up to 5 rows
rv = plpy.execute("SELECT * FROM my_table", 5)

# Access a value by row index and column name
my_col_data = rv[i]["my_column"]

The result object also supports nrows() and status(). For the full list of methods, see Database Access.

plpy.prepare()

plpy.prepare(query, arg_types) prepares an execution plan for a query. If the query contains parameter references ($1, $2, ...), pass a list of parameter types as the second argument.

# Prepare a plan with a text parameter
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])

# Execute the plan with a specific value
rv = plpy.execute(plan, ["Fred"])

Anonymous code blocks

Run a DO statement to execute an anonymous code block without creating a persistent function.

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 errors and messages

The plpy module provides message functions at different severity levels.

Function	Behavior
`plpy.debug(msg)`	Emits a DEBUG-level message.
`plpy.log(msg)`	Emits a LOG-level message.
`plpy.info(msg)`	Emits an INFO-level message.
`plpy.notice(msg)`	Emits a NOTICE-level message.
`plpy.warning(msg)`	Emits a WARNING-level message.
`plpy.error(msg)`	Raises a Python exception; cancels the current transaction or subtransaction if uncaught. Equivalent to `raise plpy.error(msg)`.
`plpy.fatal(msg)`	Raises a Python exception with FATAL severity; cancels the current transaction or subtransaction if uncaught. Equivalent to `raise plpy.fatal(msg)`.

Manage Python dependencies

Python dependencies are stored on the local SSDs of the coordinator and compute nodes. The Alibaba Cloud image website is available as a package source.

To install additional dependencies, submit a ticketsubmit a ticket.