PLV8 is a trusted JavaScript language extension for PostgreSQL. You can use JavaScript to write PostgreSQL functions. This topic describes how to install and use the PLV8 plug-in.

Benefits

  • Ease of use because it is JavaScript-based
  • Powerful performance by the V8 engine of Google
  • Abundant SQL functions

Installation and deployment

  • Install PLV8 on the database. 
    CREATE EXTENSION plv8;
  • Verify the installation. 
    SELECT plv8_version();
    If the PLV8 version is returned, the installation is successful.
  • Run the environment.

    Each session has a separate runtime environment. If you switch to another session, a new JavaScript runtime context is initialized to separate data.

  • Configure initialization settings. 
    SET plv8.start_proc = 'xxx';
    Note
    • Only administrators can configure initialization settings.
    • xxx indicates the function or variable that is set for initialization. Example:
      // Reference the startup.sql and startup_pre.sql files.
set plv8.start_proc = startup;
do $$ plv8.elog(NOTICE, 'foo = ' + foo) $$ language plv8;

Usage

PLV8 allows you to execute multiple types of function calls inside PostgreSQL, or use multiple built-in functions bound to PLV8 objects.

  • Scalar function calls
    In PLV8, you can write your function calls invoked in JavaScript by executing CREATE FUNCTION statement. Example:
    CREATE FUNCTION plv8_test(keys TEXT[], vals TEXT[]) RETURNS JSON AS $$
var o = {};
for(var i=0; i<keys.length; i++){
o[keys[i]] = vals[i];
}
return o;
$$ LANGUAGE plv8 IMMUTABLE STRICT;

=# SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);

plv8_test
---------------------------
{"name":"Tom","age":"29"}
(1 row)
    Internally, the function is defined in the following way:
    (function(keys, vals) {
var o = {};
for(var i=0; i<keys.length; i++){
o[keys[i]] = vals[i];
}
return o;
})
    Note
    • keys and vals are type checked and validated inside PostgreSQL, and called as arguments to the function.
    • o is the object that is returned as the JSON type to PostgreSQL. If argument names are omitted when you create the function, they will be available in the function as in forms such as $1 and $2.
  • Set-returning function calls
    PLV8 can return SET from function calls: 
    CREATE TYPE rec AS (i integer, t text);
CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
$$
// plv8.return_next() stores records in an internal tuplestore, 
// and return all of them at the end of function. 
plv8.return_next( { "i": 1, "t": "a" } );
plv8.return_next( { "i": 2, "t": "b" } );

// You can also return records with an array of JSON. 
return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
$$
LANGUAGE plv8;
    The following result is returned:
    SELECT * FROM set_of_records();

i | t
---+---
1 | a
2 | b
3 | c
4 | d
(4 rows)
    Note
    • If the function is declared as RETURNS SETOF, PLV8 prepares a tuplestore each time the function is called. You can call the plv8.return_next() function many times to return a row. You can also return an array to add a set of records.
    • If the argument object to return_next() has extra properties that are not defined by the argument, return_next() raises an error.
  • Trigger function calls
    PLV8 supports trigger function calls:
    CREATE FUNCTION test_trigger() RETURNS TRIGGER AS
$$
    plv8.elog(NOTICE, "NEW = ", JSON.stringify(NEW));
    plv8.elog(NOTICE, "OLD = ", JSON.stringify(OLD));
    plv8.elog(NOTICE, "TG_OP = ", TG_OP);
    plv8.elog(NOTICE, "TG_ARGV = ", TG_ARGV);
    if (TG_OP == "UPDATE") {
        NEW.i = 102;
        return NEW;
    }
$$
LANGUAGE "plv8";

CREATE TRIGGER test_trigger
    BEFORE INSERT OR UPDATE OR DELETE
    ON test_tbl FOR EACH ROW
    EXECUTE PROCEDURE test_trigger('foo', 'bar');
    If the trigger type is an INSERT or UPDATE, you can assign properties of the NEW variable to change the actual tuple stored by this operation.
    A PLV8 trigger function will have the following special arguments that contain the trigger state:
    • NEW
    • OLD
    • TG_NAME
    • TG_WHEN
    • TG_LEVEL
    • TG_OP
    • TG_RELID
    • TG_TABLE_NAME
    • TG_TABLE_SCHEMA
    • TG_ARGV
    For more information, see the Trigger Functions section in PostgreSQL documentation.
  • Inline statement calls
    In PostgreSQL 9.0 and later, PLV8 supports DO blocks. 
    DO $$ plv8.elog(NOTICE, 'this', 'is', 'inline', 'code') $$ LANGUAGE plv8;
  • Automatic mapping between JavaScript and PostgreSQL built-in types
    For the result and arguments, PostgreSQL types and JavaScript types are automatically mapped if one of the following PostgreSQL types is used:
    • oid
    • bool
    • int2
    • int4
    • int8
    • float4
    • float8
    • numeric
    • date
    • timestamp
    • timestamptz
    • bytea
    • json (>= 9.2)
    • jsonb (>= 9.4)
    If the JavaScript value is compatible, the conversion is successful. Otherwise, PLV8 tries to convert them via the cstring representation. An array type is supported only if the dimension is one. The JavaScript object is mapped to a tuple when applicable. PLV8 also supports polymorphic types such as ANYELEMENT and ANYARRAY. Conversion of BYTEA is different. For more information, see Typed arrays.
  • Typed arrays
    Typed arrays are provided to allow fast access to native memory, mainly for the purpose of their canvas support in browsers. PLV8 uses typed arrays to map BYTEA and various array types to JavaScript arrays. In the case of BYTEA, you can access each byte as an array of unsigned bytes. For int2, int4, float4, and float8 array types, PLV8 provides direct access to each element by using PLV8 domain types.
    • plv8_int2array maps int2[]
    • plv8_int4array maps int4[]
    • plv8_float4array maps float4[]
    • plv8_float8array maps float8[]
    These typed arrays are only annotations that tell PLV8 to use the fast access method instead of the regular one. Among them, only one-dimensional arrays do not contain NULL elements. You cannot create such typed array inside PLV8 functions, only arguments can be typed array. You can modify the element and return the value. An example of a typed array:
    CREATE FUNCTION int4sum(ary plv8_int4array) RETURNS int8 AS $$
  var sum = 0;
  for (var i = 0; i < ary.length; i++) {
    sum += ary[i];
  }
  return sum;
$$ LANGUAGE plv8 IMMUTABLE STRICT;

SELECT int4sum(ARRAY[1, 2, 3, 4, 5]);
 int4sum
---------
      15
(1 row)
  • Built-in functions
    PLV8 includes multiple built-in functions bound to the PLV8 object.
    • plv8.elog: sends a message to the client or PostgreSQL log file. Error levels include:
      • DEBUG5
      • DEBUG4
      • DEBUG3
      • DEBUG2
      • DEBUG1
      • LOG
      • INFO
      • NOTICE
      • WARNING
      • ERROR
      Example:
      var msg = 'world';

plv8.elog(DEBUG1, 'Hello', `${msg}!`);
    • plv8.quote_literal, plv8.nullable, and plv8.quote_ident: Each function for the quote family is identical to the built-in SQL function with the same name.
    • plv8.find_function: accesses other functions defined as PLV8 functions that have been registered in the database. 
      CREATE FUNCTION callee(a int) RETURNS int AS $$ return a * a $$ LANGUAGE plv8;
CREATE FUNCTION caller(a int, t int) RETURNS int AS $$
  var func = plv8.find_function("callee");
  return func(a);
$$ LANGUAGE plv8;
      You can use plv8.find_function() to find other PLV8 functions. If they are not a PLV8 function, an error is thrown. The function signature parameter to plv8.find_function() is either of regproc (function name only) or regprocedure (function name with argument types). You can make use of the internal type for arguments and void type for return type for the pure JavasSript function to make sure that an invocation from SQL statements does not occur.
    • plv8.version: the version string provided by the PLV8 object. This string corresponds to the PLV8 module version.
  • Database access by using SPI

    PLV8 provides functions for database access, including prepared statements, and cursors.

    • plv8.execute( sql [, args] ): executes SQL statements and retrieves the results. The sql argument is required, and the args argument is an optional array containing any arguments passed in the SQL query. For SELECT queries, the returned value is an array of objects. Each object represents one row, with the object properties mapped as column names. For non-SELECT queries, the return result is the number of rows affected. 
      var json_result = plv8.execute('SELECT * FROM tbl');
var num_affected = plv8.execute('DELETE FROM tbl WHERE price > $1', [ 1000 ]);
    • plv8.prepare( sql [, typenames] ): starts or creates a prepared statement. The typename parameter is an array where each element is a string that corresponds to the PostgreSQL type name for each bind parameter. The returned value is an object of the PreparedPlan type. This object must be freed by plan.free() before leaving the function. 
      var plan = plv8.prepare( 'SELECT * FROM tbl WHERE col = $1', ['int'] );
var rows = plan.execute( [1] );
var sum = 0;
for (var i = 0; i < rows.length; i++) {
  sum += rows[i].num;
}
plan.free();

return sum;
    • PreparedPlan.execute( [args] ): executes the prepared statement. The args parameter is the same as what is required for plv8.execute(). The args parameter can be omitted if the statement does not have any parameters. The result of this method is also the same as plv8.execute().
    • PreparedPlan.cursor( [args] ): opens a cursor from the prepared statement. The args parameter is the same as what is required for plv8.execute() and PreparedPlan.execute(). The returned object is of the cursor type. This must be closed by Cursor.close() before leaving the function. 
      var plan = plv8.prepare( 'SELECT * FROM tbl WHERE col = $1', ['int'] );
var cursor = plan.cursor( [1] );
var sum = 0, row;
while (row = cursor.fetch()) {
    sum += row.num;
}
cursor.close();
plan.free();

return sum;
    • PreparedPlan.free(): frees the prepared statement.
    • Cursor.fetch( [nrows] ): fetches a row from the cursor and returns it as an object (note: not as an array)when the nrows parameter is omitted. If the nrows parameter is specified, it fetches as many rows as defined by the nrows parameter (up to the number of rows available) and returns an array of objects. If a negative value is specified, it fetches rows backward.
    • Cursor.move( [nrows] ): moves the cursor by a value specified by the nrows parameter. If a negative value is specified, it moves the cursor backward.
    • Cursor.close(): closes the cursor.
    • plv8.subtransaction( func ): plv8.execute() creates a subtransaction each time it is executed. If you require an atomic operation, you must call plv8.subtransaction() to create a subtransaction block. 
      try{
  plv8.subtransaction(function(){
    plv8.execute("INSERT INTO tbl VALUES(1)"); // should be rolled back!
    plv8.execute("INSERT INTO tbl VALUES(1/0)"); // occurs an exception
  });
} catch(e) {
  ... do fall back plan ...
}
      Note If one of the SQL executions in the subtransaction block fails, all operations within the block are rolled back. If the process in the block throws a JavaScript exception, it is carried forward. Therefore, you can use a try ... catch block to capture an exception and do alternative operations if an exception occurs.
  • Window functions

    You can customize window functions by using PLV8. It wraps the C-level window function API to support full functionality. To create a window function, you must first call plv8.get_window_object() to create a window object. plv8.get_window_object() provides the following APIs:

    Note For more information about custom window functions, see the CREATE FUNCTION section in PostgreSQL documentation.
    • WindowObject.get_current_position(): returns the current position in the partition, starting from 0.
    • WindowObject.get_partition_row_count(): returns the number of rows in a partition.
    • WindowObject.set_mark_position( pos ): sets the mark at the specified row. Rows above this position is invisible and no longer accessible later.
    • WindowObject.rows_are_peers( pos1, pos2 ): Returns true if the rows at pos1 and pos2 are peers.
    • WindowObject.get_func_arg_in_partition( argno, relpos, seektype, mark_pos ) and WindowObject.get_func_arg_in_frame( argno, relpos, seektype, mark_pos ):

      Returns the value of the argument in argno (starting from 0) to this function at the relpos row from seektype in the current partition or frame. seektype can be WindowObject.SEEK_HEAD, WindowObject.SEEK_CURRENT, or WindowObject.SEEK_TAIL. If mark_pos is true, the row from which the argument is fetched is marked. If the specified row is out of the partition or frame, the returned value is undefined.

    • WindowObject.get_func_arg_in_current( argno ): returns the value of the argument in argno (starting from 0) to the function at the current row.
      Note The returned value is the same as the argument of the function.
    • WindowObject.get_partition_local( [size] ): returns the partition-local value, which is released at the end of the current partition. If nothing is stored, undefined is returned. The size argument (default value: 1000) is the byte size of the allocated memory in the first call. After the memory is allocated, the size value does not change.
    • WindowObject.set_partition_local( obj ): stores the partition-local value, which you can retrieve later by using get_partition_local(). This function internally uses JSON.stringify() to serialize the object. Therefore, an unexpected value may be returned if you pass a value that is not able to be serialized. If the size of a serialized value is more than the allocated memory, it throws an exception.