This is the second part of PostgreSQL, Node.js and JavaScript post.
We can continue by installing PL/V8:
sudo apt-get install postgresql-9.2-plv8
Then we can create the extension in our database.
sudo su postgres
psql mydb
And run SQL command below:
create extension plv8;
Then, we can create functions using PL/V8 JavaScript language. A simple example is below:
CREATE FUNCTION public.service (
inputs pg_catalog.json
)
RETURNS pg_catalog.json AS
$body$
var result = {};
var cmd = 'SELECT * FROM tbl_test';
result.outputs = plv8.execute(cmd);
result.row_count = result.outputs.length;
return result;
$body$
LANGUAGE 'plv8'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
It gets variable inputs as JSON type. It returns a single variable result as JSON type. The inputs can have the structure for various parameters so that we can filter and return some records from a table or view. The outputs can include status, rows, error messages, etc. The next example is slightly more complicated:
CREATE OR REPLACE FUNCTION public.service_search (
inputs pg_catalog.json
)
RETURNS pg_catalog.json AS
$body$
var r = {};
r.status = '';
r.messages = [];
r.outputs = [];
r.row_count = 0;
if (!inputs.limit || inputs.limit < 0 || inputs.limit > 100) inputs.limit = 100;
if (!inputs.offset || inputs.offset < 0) inputs.offset = 0;
try {
r.messages[0] = {"field": "view", "valid": false};
r.messages[1] = {"field": "id", "valid": false};
r.messages[2] = {"field": "name", "valid": false};
if (inputs.view && inputs.view != '') {
r.messages[0].valid = true;
} else {
throw 'Invalid view';
}
var cmd = 'SELECT * FROM ' + inputs.view;
var criteria = '';
if (inputs.id && inputs.id > 0) {
r.messages[1].valid = true;
criteria += '(id >= $1)';
}
if (inputs.name && inputs.name != '') {
r.messages[2].valid = true;
criteria += (criteria != '' ? ' and ': '') + '(name like $2)';
}
cmd += ' limit ' + inputs.limit;
cmd += ' offset ' + inputs.offset;
r.outputs = plv8.execute(cmd);
r.row_count = r.outputs.length;
r.status = 'success';
} catch (ex) {
r.status = 'error';
r.error = ex;
}
return r;
$body$
LANGUAGE 'plv8'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
This is a proof of concept that we can have a set of functions to provide CRUD operations, for instance, and even more advanced requirements. To be honest, for now, I cannot see a straight forward way to have OOP coding using this method.