A stored procedure of AnalyticDB for PostgreSQL contains a set of SQL statements. These SQL statements are permanently valid after they are compiled.

To run a stored procedure, you must specify the name and parameters of the stored procedure. AnalyticDB for PostgreSQL supports stored procedures in the following languages:

  • PL/pgSQL - SQL procedural language
  • PL/Python - Python procedural language
  • PL/Java - Java procedural language

You can use a procedural language to create a stored procedure.

PL/pgSQL - SQL procedural language

PL/pgSQL is an easy-to-use procedural language that comes with AnalyticDB for PostgreSQL. PL/pgSQL is designed to create a loadable procedural language that provides the following features:

  • Creates functions and trigger procedures.
  • Adds control structures to SQL procedure languages.
  • Performs complex computing.
  • Inherits user-defined types, functions, and operators.
  • Trusted by servers.

Functions created in PL/pgSQL and built-in functions of AnalyticDB for PostgreSQL can be used in the same scenarios. For example, you can create complex conditional computing functions and use the functions to define operators and index functions.

PL/Python - Python procedural language

PL/Python allows you to compile procedural functions of AnalyticDB for PostgreSQL in Python. To install PL/Python in a database, execute the CREATE EXTENSION plpythonu statement or run the createlang plpythonu dbname command in the command line.

Note After a language is installed into the template1 database, the language is automatically installed into all databases that you create later.

PL/Python is considered an untrusted language and does not provide a method to limit user operations. If you use PL/Python to develop functions, you must take note of the constraints.

PL/Java - Java procedural language

You can use PL/Java extensions of AnalyticDB for PostgreSQL databases to develop Java methods in your Java integrated development environment (IDE) and install the JAR files that contain the Java methods in the AnalyticDB for PostgreSQL databases.

The PL/Java package of AnalyticDB for PostgreSQL databases is developed based on open source PL/Java 1.4.0 and provides the following features:

  • Uses Java 8 or Java 1 to execute PL/Java functions.
  • Provides a standardized mapping between parameters and results. Supports complex type sets.
  • Supports embedded, high-performance JDBC drivers that use the internal Serial Peripheral Interface (SPI) of AnalyticDB for PostgreSQL databases.
  • Supports metadata of JDBC drivers, including DatabaseMetaData and ResultSetMetaData.
  • Returns results by row from the result set of the query.
  • Supports savepoints and exception handling.
  • Supports the IN, INPUT, and OUT parameters.
  • Compiles and executes a transaction listener or a savepoint listener when a transaction or a savepoint is committed or rolled back.

An SQL function corresponds to a static method specified in the Java class. The specified class must be able to specify the class path by using the pljava_classpath configuration parameter in the database server of AnalyticDB for PostgreSQL.

AnalyticDB for PostgreSQL allows you to execute the CREATE LIBRARY statement to install the user-compiled JAR files into databases. You do not need to configure pljava_classpath.