plv8: JavaScript Language

The plv8 extension allows you use JavaScript within Postgres.

Overview

While Postgres natively runs SQL, it can also run other "procedural languages". plv8 allows you to run JavaScript code - specifically any code that runs on the V8 JavaScript engine.

It can be used for database functions, triggers, queries and more.

Usage

Enable the extension

Dashboard

1. Go to the Database page in the Dashboard.
2. Click on Extensions in the sidebar.
3. Search for "plv8" and enable the extension.

SQL

-- Example: enable the "plv8" extension 
create extension plv8;

-- Example: disable the "plv8" extension 
drop extension if exists plv8;

Even though the SQL code is create extension, this is the equivalent of "enabling the extension". To disable an extension, call drop extension.

Procedural languages are automatically installed within pg_catalog, so you don't need to specify a schema.

Create plv8 functions

Functions written in plv8 are written just like any other PostgreSQL functions, only with the language identifier set to plv8.

create or replace function function_name() 
returns void as $$
    // V8 JavaScript
    // code
    // here
$$ language plv8;

You can call plv8 functions like any other Postgres function:

SQL

select function_name();

JavaScript

const { data, error } = supabase.rpc('function_name')

Examples

Scalar functions

A scalar function is anything that takes in some user input and returns a single result.

create or replace function hello_world(name text) 
returns text as $$

    let output = `Hello, ${name}!`;
    return output;

$$ language plv8;

Executing SQL

You can execute SQL within plv8 code using the plv8.execute function.

create or replace function update_user(id bigint, first_name text) 
returns smallint as $$

    var num_affected = plv8.execute(
        'update profiles set first_name = $1 where id = $2', 
        [first_name, id]
    );

    return num_affected;
$$ language plv8;

Set-returning functions

A set-returning function is anything that returns a full set of results - for example, rows in a table.

create or replace function get_messages() 
returns setof messages as $$

    var json_result = plv8.execute(
        'select * from messages'
    );

    return json_result;
$$ language plv8;

Resources

• Official plv8 documentation
• plv8 GitHub Repository