Skip to content

Instantly share code, notes, and snippets.

@rafaelveloso
Last active December 6, 2019 14:21
Show Gist options
  • Save rafaelveloso/963c0ff0641ff56425d0 to your computer and use it in GitHub Desktop.
Save rafaelveloso/963c0ff0641ff56425d0 to your computer and use it in GitHub Desktop.
How to load Javascript modules into postgres using plv8
/******************************************************************************
How to load Javascript modules into postgres
******************************************************************************/
CREATE EXTENSION IF NOT EXISTS plv8
/******************************************************************************
First step is download the Javascript module file
Example with undescore-min and node-jpath
******************************************************************************/
wget http://underscorejs.org/underscore-min.js
wget https://raw.github.com/adunstan/plv8-jpath/master/lib/plv8-jpath.js
/******************************************************************************
Open postgres console and set runtime variable to
use the code later
******************************************************************************/
psql -d <database>
\set underscore `cat underscore-min.js`
\set jpath `cat plv8-jpath.js`
/******************************************************************************
Now that we have set variable containing the code
we need to create a table to store each of them in
postgres.
******************************************************************************/
create table plv8_modules(modname text primary key, load_on_start boolean, code text);
insert into plv8_modules values ('underscore',true,:'underscore'),
('jpath',true,:'jpath');
/******************************************************************************
Create a a startup function to create a plv8 function
that will be used to load the modules, Executing it
will register the plv8 function
******************************************************************************/
create or replace function plv8_startup()
returns void
language plv8
as
$$
load_module = function(modname) {
var rows = plv8.execute("SELECT code from plv8_modules " +" where modname = $1", [modname]);
for (var r = 0; r < rows.length; r++) {
var code = rows[r].code;
eval("(function() { " + code + "})")();
}
};
$$;
select plv8_startup();
/******************************************************************************
Load both modules into postgres using the previously created plv8 function
******************************************************************************/
do language plv8 ' load_module("underscore"); load_module("jpath"); ';
/******************************************************************************
Test the underscore module's extend function
******************************************************************************/
do language plv8 $$
x = { 'a':1 };
y=_.extend(x, { 'a':2,'b':3 }, { 'b':4, 'c':5 });
plv8.elog(NOTICE,JSON.stringify(y));
$$;
/******************************************************************************
Test jpath module's filter function (github module example)
******************************************************************************/
do language plv8 $$
var jsonData = {
people: [
{ name: "John", age: 26, gender: "male" },
{ name: "Steve", age: 24, gender: "male" },
{ name: "Susan", age: 22, gender: "female" },
{ name: "Linda", age: 30, gender: "female" },
{ name: "Adam", age: 32, gender: "male"}
]
};
//Get all males younger then 25
var match = jpath.filter(jsonData, "people[gender=male && age < 25]");
plv8.elog(NOTICE,JSON.stringify(match));
$$;
@jamesdixon
Copy link

This is great. Do you happen to have any suggestions how to load this automatically on startup and have it available on all connections? For some reason, anytime I make a new connection to the database (from the application or the command line), it acts like the load_module function hasn't been run. If I run it manually, it works for that connection, but doesn't for others. Thanks!

@ppKrauss
Copy link

Hi @rafaelveloso and @jamesdixon, seems perfect (!), can you confirm here?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment