PLV8

PLV8 is a trusted Javascript language extension for PostgreSQL. It can be
used for stored procedures, triggers, etc.

PLV8 works with most versions of Postgres, but works best with 9.1 and above,
including 10.0 and 10.1.

Installing PLV8

If the PLV8 extension has been installed to your system, the PLV8 extension can
be installed into your PostgreSQL database by running:

=# CREATE EXTENSION plv8;

Verifying Your Installation

You can verify the installation in two ways. As of PLV8 2.0.0, you can
execute a stored procedure:

=# SELECT plv8_version();

Alternately, you can run the following on all versions of PLV8:

=# DO $ plv8.elog(NOTICE, plv8.version); $ LANGUAGE plv8;

Updating PLV8

As of PLV8 version 2.3.3, you can use upgrade scripts to upgrade your
installation from any verion higher than 1.5.0:

=# ALTER EXTENSION plv8 UPDATE TO `2.3.3`;

Note that until the database has been restarted, the old version of PLV8 will
still be loaded, though SELECT plv8_version() will return the new version.
This is an artifact of how Postgres manages extensions.

Updating Older PLV8 Installs

Updating PL/v8 is usually straightforward as it is a small and stable extension

it only contains a handful of objects that need to be added to PostgreSQL
when installing the extension.

The procedure that is responsible for invoking this installation script
(generated during compile time based on plv8.sql.common), is controlled by
PostgreSQL and runs when CREATE EXTENSION is executed only. After building, it
takes the form of plv8--.sql and is usually located under
/usr/share/postgresql/<PG_MAJOR>/extension, depending on the OS.

When this command is executed, PostgreSQL tracks which objects belong to the
extension and conversely removes them upon uninstallation, i.e., whenever
DROP EXTENSION is called.

You can explore some of the objects that PL/v8 stores under PostgreSQL:

Runtime Environment Separation

In PLV8, each session has one global JS runtime context. This enables function
invocations at low cost, and sharing common object among the functions. However,
for the security reasons, if the user switches to another with SET ROLE command,
a new JS runtime context is initialized and used separately. This prevents the
risk of unexpected information leaking.

Each plv8 function is invoked as if the function is the property of other
object. This means this in each function is a Javascript object that is created
every time the function is executed in a query. In other words, the life time and
the visibility of this object in a function is only a series of function calls in
a query. If you need to share some value among different functions, keep it in the
global plv8 object because each function invocation has a different this object.

Start-up Procedure

PLV8 provides a start up facility, which allows you to call a plv8 runtime
environment initialization function specified in the GUC variable. This can
only be set by someone with administrator access to the database you are
accessing.

SET plv8.start_proc = 'plv8_init';
SELECT plv8_test(10);

If this variable is set when the runtime is initialized, before the function
call of plv8_test() another plv8 function plv8_init() is invoked. In such
initialization function, you can add any properties to plv8 object to expose
common values or assign them to the this property. In the initialization function,
the receiver this is specially pointing to the global object, so the variables
that are assigned to the this property in this initialization are visible from
any subsequent function as global variables.

Remember CREATE FUNCTION also starts the plv8 runtime environment, so make
sure to SET this GUC before any plv8 actions including CREATE FUNCTION.

Downloading Source

Building

This will download v8 and compile it as well. If you have a shared modern
version of v8 available (6.4.388.40 or above), you can compile it against a
shared module:

$ make -f Makefile.shared

| Note: If you have multiple versions of PostgreSQL installed like 9.5 and 9.6, Plv8 will only be built for PostgreSQL 9.6. This is because make calls pg_config to get the version number, which will always be the latest version installed. If you need to build Plv8 for PostgreSQL 9.5 while you have 9.6 installed pass make the PG_CONFIG variable to your 9.5 version of pg_config. This works for make, make -f Makefile.shared, and make install. For example in Ubuntu:

$ make PG_CONFIG=/usr/lib/postgresql/9.5/bin/pg_config

Building with Execution Timeout

Plv8 allows you to optionally build with an execution timeout for Javascript
functions, when enabled at compile-time.

$ make EXECUTION_TIMEOUT=1

By default, the execution timeout is not compiled, but when configured it has a
timeout of 300 seconds (5 minutes). You can override this by setting the
plv8.execution_timeout variable. It can be set between 1 second and 65536
seconds, but cannot be disabled.

Building with ICU

Building with ICU requires you to enable ICU in your build process:

$ make USE_ICU=1

If you build with ICU, you will need to install the correct ICU file, located in
contrib/icu.

icudtl.dat - Little Endian architectures (Intel)

icudtb.dat - Big Endian architectures (Sparc)

For ARM, you will need to figure out which Endianess your hardware and OS is
configured for.

NOTE: it is important that the user that Postgres is started with has read
access to the file.

Installing

Once PLV8 has been built, you need to install it for PostgreSQL to be able to use
it:

$ make install

This might require root access, depending on how PostgreSQL is installed:

$ sudo make install

Testing

Once PLV8 is installed, you can verify the install by running:

$ make installcheck

Building for Windows

Building PLV8 for Windows has some specific requirements:

Git

MSVC 2013, 2015, or 2017

CMake - available as part of MSVC

Postgres 9.5+ (it will work in 9.3 and 9.4, but will involve extra work)

Additional requirements to build V8:

Python 2

unzip.exe

patch.exe - part of the Git install

Patching Postgres

Currently, Postgres requires a patch of one or more include files in order to
compile PLV8.

First, find the directory that contains the include files. This will typically
be inside something like C:\Program Files\PostgreSQL\10\include, where the 10
is your version number. Inside of the include directory:

Bootstrapping

Bootstrapping will the build environment, download, and compile v8. Watch for
any errors:

PS> bootstrap.bat

Configuring

Once v8 has been built, you can configure your build environment. This involves
specifying the path to your Postgres install, the version of Postgres you are
running, as well as the build target. Build targets will typically be one of the
following:

Where keys and vals are type checked and validated inside of PostgreSQL,
called as arguments to the function, and o is the object that is returned as
the JSON type back to PostgreSQL. If argument names are omitted in the creation
of the function, they will be available in the function as $1, $2, etc.

Internally, if the function is declared as RETURNS SETOF, PLV8 prepares a
tuplestore every time every time it is called. You can call the
plv8.return_next() function as many times as you need to return a row. In
addition, 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.

If the trigger type is an INSERT or UPDATE, you can assign properties of
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:

Inline Statement Calls

Auto Mapping Between Javascript and PostgreSQL Built-in Types

For the result and arguments, PostgreSQL types and Javascript types are mapped
automatically, if the desired PostgreSQL type is one of:

OID

bool

INT3

INT4

INT8

FLOAT4

FLOAT8

NUMERIC

DATE

TIMESTAMP

TIMESTAMPTZ

BYTEA

JSON (>= 9.2)

JSONB (>= 9.4)

and the Javascript value looks compatible, then the conversion succeeds.
Otherwise, PLV8 tries to convert them via the cstring representation. An
array type is supported only if the dimension is one. A Javascript object
will be mapped to a tuple when applicable. In addition to these types, PLV8
supports polymorphic types such like ANYELEMENT and ANYARRAY. Conversion of
BYTEA is a little different story. See the TypedArray section.

Typed Array

The typed array is something v8 provides to allow fast access to native
memory, mainly for the purpose of their canvas support in browsers. PLV8 uses
this to map BYTEA and various array types to a Javascript array. In the case
of BYTEA, you can access each byte as an array of unsigned bytes. For
int2/int4/float4/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 are only annotations that tell PLV8 to use the fast access method instead
of the regular one. For these typed arrays, only 1-dimensional arrays without
any NULL elements. There is currently no way to create such typed array inside
PLV8 functions, only arguments can be typed array. You can modify the element and
return the value. An example for these types are as follows:

With plv8.find_function()`, you can look up other PLV8 functions. If they
are not a PLV8 function, and 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 Javascript
function to make sure any invocation from SQL statements should not occur.

plv8.version

The plv8 object provides a version string as plv8.version. This string
corresponds to the plv8 module version.

Database Access via SPI

plv8.execute

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.

plv8.prepare

plv8.prepare(sql [, typenames])

Opens 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. Returned value is an object of the PreparedPlan type.
This object must be freed by plan.free() before leaving the function.

PreparedPlan.execute

PreparedPlan.execute([ args ])

Executes the prepared statement. The args parameter is the same as what would be
required for plv8.execute(), and 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

PreparedPlan.cursor([ args ])

Opens a cursor form the prepared statement. The args parameter is the same as
what would be required for plv8.execute() and PreparedPlan.execute(). The
returned object is of type Cursor. This must be closed by Cursor.close()
before leaving the function.

PreparedPlan.free

Frees the prepared statement.

Cursor.fetch

Cursor.fetch([ nrows ])

When the nrows parameter is omitted, fetches a row from the cursor and returns
it as an object (note: not as an array). If specified, fetches as many rows
as the nrows parameter, up to the number of rows available, and returns an
array of objects. A negative value will fetch backward.

Cursor.move

Cursor.move(nrows)

Moves the cursor nrows. A negative value will move backward.

Cursor.close

Closes the Cursor.

plv8.subtransaction

plv8.subtransaction(func)

plv8.execute() creates a subtransaction each time it executes. If you need
an atomic operation, you will need to call plv8.subtransaction() to create
a subtransaction block.

If one of the SQL execution in the subtransaction block fails, all of operations
within the block are rolled back. If the process in the block throws a Javascript
exception, it is carried forward. So use a try ... catch block to capture it and
do alternative operations if it occurs.

Window Function API

You can define user-defined window functions with PLV8. It wraps the C-level
window function API to support full functionality. To create one, first obtain a
window object by calling plv8.get_window_object(), which provides the following
interfaces:

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 the partition.

WindowObject.set_mark_position

WindowObject.set_mark_position(pos)

Sets the mark at the specified row. Rows above this position will be gone and
no longer accessible later.

WindowObject.rows_are_peers

WindowObject.rows_are_peers(pos1, pos1)

Returns true if the rows at pos1 and pos2 are peers.

WindowObject.get_func_arg_in_partition

WindowObject.get_func_arg_in_frame

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 either of WindowObject.SEEK_HEAD, WindowObject.SEEK_CURRENT,
or WindowObject.SEEK_TAIL. If mark_pos is true, the row the argument is
fetched from is marked. If the specified row is out of the partition/frame, the
returned value will be undefined.

WindowObject.get_func_arg_in_current

WindowObject.get_func_arg_in_current(argno)

Returns the value of the argument in argno (starting from 0) to this function
at the current row. Note that the returned value will be the same as the argument
variable of the function.

WindowObject.get_partition_local

WindowObject.get_partition_local([ size ])

Returns partition-local value, which is released at the end of the current
partition. If nothing is stored, undefined is returned. size argument (default
1000) is the byte size of the allocated memory in the first call. Once the memory
is allocated, the size will not change.

WindowObject.set_partition_local

WindowObject.set_partition_local(obj)

Stores the partition-local value, which you can retrieve later with
get_partition_local()`. This function internally uses JSON.stringify() to\
serialize the object, so if you pass a value that is not able to be serialized
it may end up being an unexpected value. If the size of a serialized value is
more than the allocated memory, it will throw an exception.

You can also learn more on how to use these API in the sql/window.sql
regression test, which implements most of the native window functions. For
general information on the user-defined window function, see the CREATE FUNCTION page of the PostgreSQL manual.