Procedural Languages

Postgres by BigSQL includes several procedural languages (PLs) that enable functions to be written in languages other than SQL and C. The PLs are pre-built as PostgreSQL extensions and can be easily enabled in each database to write functions in the language of your choice.

Included Languages

PL/pgSQL

PL/pgSQL is included and enabled by default. Simply create functions and use:


  mydb=# CREATE FUNCTION pgMax(a integer, b integer)
  mydb-#   RETURNS integer
  mydb-# as $$
  mydb$# BEGIN
  mydb$#   IF a > b THEN
  mydb$#     RETURN a;
  mydb$#   END IF;
  mydb$#   RETURN b;
  mydb$# END;
  mydb$# $$ LANGUAGE plpgsql;
  CREATE FUNCTION
  mydb=#
  mydb=# select pgMax(4, 8);
   pgmax
  -------
       8
  (1 row)

  mydb=# DROP FUNCTION pgMax(integer, integer);
  DROP FUNCTION
  mydb=#

Learn More

To learn more about PL/pgSQL, please see the PostgreSQL docs.

PL/Python

PL/Python 2 is included and enables Python 2 to be used to create functions.

The Python executable must be on the path and PYTHONHOME and PYTHONPATH environment variables must be set. In order to use PL/Python, create the plpythonu extension:


  mydb=# CREATE EXTENSION plpythonu;
  CREATE EXTENSION

Then create and use functions written in Python:


  mydb=# CREATE FUNCTION pymax (a integer, b integer)
  mydb-#    RETURNS integer
  mydb-# AS $$
  mydb$#  if a > b:
  mydb$#    return a
  mydb$#  return b
  mydb$# $$ LANGUAGE plpythonu;
  CREATE FUNCTION
  mydb=#
  mydb=# SELECT pymax(1, 4);
   pymax
  -------
       4
  (1 row)

To remove a function or the PL/Python extension, simply drop them:


  mydb=# DROP FUNCTION pymax(integer, integer);
  DROP FUNCTION
  mydb=#
  mydb=# DROP EXTENSION plpythonu;
  DROP EXTENSION
  mydb=#

Learn More

To learn more about PL/Python, please see the PostgreSQL docs.

PL/Perl

PL/Perl is included in and enables Perl to be used to create functions. To use, Perl must be installed and configured.

Create Extension

Once perl is installed, create the plperl extension by running the following sql in your database:


  mydb=# CREATE EXTENSION plperl;
  CREATE EXTENSION

Then create and use functions written in Perl:


  mydb=# CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
  mydb$#     if ($_[0] > $_[1]) { return $_[0]; }
  mydb$#     return $_[1];
  mydb$# $$ LANGUAGE plperl;
  CREATE FUNCTION
  mydb=#
  mydb=# SELECT perl_max(1, 4);
   perl_max
  ----------
          4
  (1 row)

To remove a function or the PL/Perl extension, simply drop them:


  mydb=# DROP FUNCTION perl_max(integer, integer);
  DROP FUNCTION
  mydb=#
  mydb=# DROP EXTENSION plperl;
  DROP EXTENSION
  mydb=#

Learn More

To learn more about PL/Perl, please see the PostgreSQL docs.