Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save tkalfigo/af5f590aee9c5f4fb5c6 to your computer and use it in GitHub Desktop.
Save tkalfigo/af5f590aee9c5f4fb5c6 to your computer and use it in GitHub Desktop.
/******************************************************************************
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));
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment