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.

Tags: , , ,
Categories: