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.

PL/Python requires Python 2.7 which is available on all OS X and Linux operating systems and is included with BigSQL on Windows. 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=#

Requires: Python 2.7 (included in Windows packaging)

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.

Windows

On Windows, you need to first use the PGC Update Manager to install perl before using PL/Perl. This installs the open source Strawberry Perl flavor:


  C:\BigSQL>pgc install perl5
    ['perl5']
  Get:1 http://s3.amazonaws.com/pgcentral perl5-5.20.3.1-win64
    Unpacking perl5-5.20.3.1-win64.tar.bz2

OS X

OS X (10.9, 10.10, & 10.11) comes with perl installed, but you need to add the perl shared object to your dynamic library path environment variable and restart Postgres:


$ export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/System/Library/Perl/5.16/darwin-thread-multi-2level/CORE/
$ ./pgc stop pg95
pg95 stopping
$ ./pgc start pg95
pg95 starting on port 5432

Linux

For CentOS 6 and 7, install perl through yum. For other Linux distributions, ensure that perl 5.10.1 is installed and on the path for Postgres. You may need to add libperl.so to your LD_LIBRARY_PATH as well.

Create Extension

Once perl is configured correctly, 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=#

Requires: Perl 5.12 (installed by default on OSX and Linux, available through PGC on Windows)

Learn More

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

PL/Tcl

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

Similar to PL/Perl, in order to use PL/Tcl you have to have TCL installed and on the path. On OSX and Linux Tcl is installed and on the path by default, so you're all set. For Windows, use the PGC Update Manager to install Tcl and you're good to go:


  C:\BigSQL>pgc install tcl86
    ['tcl86']
  Get:1 http://s3.amazonaws.com/pgcentral tcl86-8.6.4.1-win64
    Unpacking tcl86-8.6.4.1-win64.tar.bz2

In order to use PL/Tcl, create the pltcl extension:


  mydb=# CREATE EXTENSION pltcl;
  CREATE EXTENSION

Then create and use functions written in Tcl:


  mydb=# CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
  mydb$#     if {$1 > $2} {return $1}
  mydb$#     return $2
  mydb$# $$ LANGUAGE pltcl STRICT;
  CREATE FUNCTION
  mydb=#
  mydb=# SELECT tcl_max(1, 4);
   tcl_max
  ---------
         4
  (1 row)

  mydb=#

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


  mydb=# DROP FUNCTION tcl_max(integer, integer);
  DROP FUNCTION
  mydb=#
  mydb=# DROP EXTENSION pltcl;
  DROP EXTENSION
  mydb=#

Requires: Tcl (installed by default on OSX and Linux, available through PGC on Windows)

Learn More

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

PL/Java

PL/Java enables Java to be used to create functions and is available as a pre-built Postgres extension installed through BigSQL Manager or the PGC command line.

  ./pgc install pljava15-pg95

If Java is not yet installed on your system, you can use pgc to install a copy of Java 8.


  C:\Program Files\PostgreSQL>pgc install java8
    ['java8']
  Get:1 http://s3.amazonaws.com/pgcentral java8-8u74-win64
    Unpacking java8-8u74-win64.tar.bz2

In order to use PL/Java, you must first set a configuration variable for PL/Java to know which jvm to use and then create the extension:


  mydb=# set pljava.libjvm_location='C:/PROGRA~1/POSTGR~1/java8/jre/bin/server/jvm.dll';
  SET
  mydb=# CREATE EXTENSION pljava;
  CREATE EXTENSION

Then create and use functions written in Java:


  mydb=# create function java_max(a integer, b integer)
  mydb-# returns integer
  mydb-# language java volatile
  mydb-# as 'java.lang.Math.max(int, int)'
  mydb-# ;
  CREATE FUNCTION
  postgres=# select java_max(2, 4);
   java_max
  ----------
          4
  (1 row)

  mydb=#

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


  mydb=# drop extension pljava cascade;
  NOTICE:  drop cascades to function java_max(integer,integer)
  DROP EXTENSION
  mydb=#

Learn More

To learn more about PL/Java including how to compile and upload your own Java classes, please see the PL/Java docs. These include additional information on available configuration options in the installation guide.

PL/v8 (Javascript)

PL/v8 is available on Linux & OSX and enables Javascript (as well as CoffeeScript and LiveScript) to be used to create functions. Install the PL/v8 extension using BigSQL Manager or the PGC command line:

  ./pgc install plv814-pg95

In order to use PL/v8, create the plv8 extension:


  mydb=# CREATE EXTENSION plv8;
  CREATE EXTENSION

Then create and use functions your own functions written in Javascript:


  mydb=# CREATE FUNCTION plv8max (a integer, b integer)
  mydb-# RETURNS integer AS $$
  mydb$#   if (a > b)
  mydb$#     return a;
  mydb$#   else
  mydb$#     return b;
  mydb$# $$ LANGUAGE plv8;
  CREATE FUNCTION
  mydb=#
  mydb=# select plv8max(5,4);
   plv8max
  ---------
         5
  (1 row)

  mydb=# select plv8max(3,4);
   plv8max
  ---------
         4
  (1 row)

Or, leverage existing Javascript libraries and functions by exposing them as functions:


  CREATE OR REPLACE FUNCTION v8_max(num1 int, num2 int)
    RETURNS int AS $$
      return Math.max(num1,num2);
    $$ LANGUAGE plv8;

PL/v8 also supports using CoffeScript or LiveScript - just create the specific extensions:


  mydb=# CREATE EXTENSION plcoffee;
  CREATE EXTENSION
  mydb=# CREATE EXTENSION plls;
  CREATE EXTENSION

To remove any of the PL/v8 extensions, simply drop them:


  mydb=# drop extension plv8 cascade;
  mydb=# drop extension plcoffee cascade;
  mydb=# drop extension plls cascade;

Learn More

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