pljs

pig build pkg pljs         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

CREATE EXTENSION pljs;

CREATE EXTENSION pljs;
DO $$ pljs.elog(NOTICE, "Hello, World!") $$ LANGUAGE pljs;

CREATE FUNCTION pljs_add(a int, b int) RETURNS int AS $$
  return a + b;
$$ LANGUAGE pljs;

SELECT pljs_add(1, 2);  -- 3

CREATE FUNCTION get_users() RETURNS SETOF json AS $$
  var rows = pljs.execute('SELECT * FROM users');
  for (var i = 0; i < rows.length; i++) {
    pljs.return_next(JSON.stringify(rows[i]));
  }
$$ LANGUAGE pljs;

var rows = pljs.execute('SELECT * FROM tbl WHERE id = $1', [42]);
var affected = pljs.execute('DELETE FROM tbl WHERE price > $1', [1000]);

var plan = pljs.prepare('SELECT * FROM tbl WHERE col = $1', ['int']);
var rows = plan.execute([1]);
plan.free();

var plan = pljs.prepare('SELECT * FROM tbl WHERE col = $1', ['int']);
var cursor = plan.cursor([1]);
var row;
while (row = cursor.fetch()) {
    // process row
}
cursor.close();
plan.free();

try {
  pljs.subtransaction(function() {
    pljs.execute("INSERT INTO tbl VALUES(1)");
    pljs.execute("INSERT INTO tbl VALUES(1/0)"); // error - rolls back
  });
} catch(e) {
  // handle error
}

pljs.elog(DEBUG1, 'debug message');
pljs.elog(NOTICE, 'notice message');
pljs.elog(WARNING, 'warning message');
pljs.elog(ERROR, 'error message');

CREATE FUNCTION callee(a int) RETURNS int AS $$ return a * a $$ LANGUAGE pljs;
CREATE FUNCTION caller(a int, t int) RETURNS int AS $$
  var func = pljs.find_function("callee");
  return func(a);
$$ LANGUAGE pljs;

CREATE FUNCTION my_window_func(val int) RETURNS int AS $$
  var winobj = pljs.get_window_object();
  var pos = winobj.get_current_position();
  var total = winobj.get_partition_row_count();
  return winobj.get_func_arg_in_current(0);
$$ LANGUAGE pljs WINDOW;

SELECT pljs_info();     -- memory and stack usage as JSON
SELECT pljs_version();  -- extension version