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
- SQL
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "plv8" and enable the extension.
-- 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
- JavaScript
select function_name();
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;