PLV8JS and PLCoffee Part 1: Upserting   

Setting up the tables

We'll use this table and add more tables to this model in later articles:

CREATE TABLE inventory (prod_code varchar(20) primary key
    , prod_name varchar(50)
    , loc_id integer
    , date_add timestamptz DEFAULT CURRENT_TIMESTAMP
    , date_upd timestamptz DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO inventory (prod_code, prod_name, loc_id)
VALUES ('BB1', 'Breadboard 174mm x 67mm', 1)
    , ('SIR', 'Solder Iron', 2)
    , ('TI_TMS5100', 'Texas Instruments TMS5100', 3); 

The Upsert Stored procedure

PL/Javascript implementation

-- this will take a json dataset as input and do a data merge

CREATE OR REPLACE FUNCTION upsert_inventory(param_inv json) RETURNS
text AS $$
var o = JSON.parse(param_inv);
/** update plan **/
var uplan = plv8.prepare('UPDATE inventory SET prod_name = $1, loc_id = $2 WHERE prod_code = $3', ['text', 'int', 'text'] );
/** insert plan **/
var iplan = plv8.prepare('INSERT INTO inventory( prod_name, loc_id, prod_code) VALUES($1, $2, $3)', ['text', 'int', 'text'] );
var num_changed;
var num_ins = 0, num_upd = 0;
if (typeof o != 'object')
   return NULL;
else {
    for(var i=0; i<o.length; i++){
      num_changed = uplan.execute([o[i].prod_name, o[i].loc_id, o[i].prod_code]);
      num_upd += num_changed;
      if (num_changed == 0){ /** we got no updates, so insert **/
        num_changed = iplan.execute([o[i].prod_name, o[i].loc_id, o[i].prod_code]);
        num_ins += num_changed;
      } 
    } 
}
iplan.free();
uplan.free();
return num_upd + ' record(s) updated and ' + num_ins + ' records inserted';
$$ LANGUAGE plv8 VOLATILE;


PL/CoffeeScript equivalent
CREATE OR REPLACE FUNCTION upsert_inventory(param_inv json) RETURNS
text AS $$
o = JSON.parse(param_inv)
# update plan 
uplan = plv8.prepare("UPDATE inventory SET prod_name = $1, loc_id = $2 WHERE prod_code = $3", [ "text", "int", "text" ])
# insert plan
iplan = plv8.prepare("INSERT INTO inventory( prod_name, loc_id, prod_code) VALUES($1, $2, $3)", [ "text", "int", "text" ])
num_changed = undefined
num_ins = 0
num_upd = 0
unless typeof o is "object"
  return NULL
else
  i = 0
  while i < o.length
    num_changed = uplan.execute([ o[i].prod_name, o[i].loc_id, o[i].prod_code ])
    num_upd += num_changed
    if num_changed is 0 # we got no updates, so insert 
      num_changed = iplan.execute([ o[i].prod_name, o[i].loc_id, o[i].prod_code ])
      num_ins += num_changed
    i++
iplan.free()
uplan.free()
return num_upd + ' record(s) updated and ' + num_ins + ' records inserted'$$ 
LANGUAGE plcoffee VOLATILE;


Testing out the stored proc

-- this is to simulate data coming from jquery
-- or some other javascript client api
-- we aggregate all rows we want to change in an array
-- and then we convert the array of rows to
-- a single json object that will serve as our dataset
SELECT upsert_inventory(array_to_json(array_agg(inv)) )
FROM (SELECT replace(prod_name, 'Bread', 'Butter') As prod_name
 , loc_id
 , replace(prod_code, 'SIR', 'SIR2') as prod_code
FROM inventory ) As inv;


-- output of query --
2 record(s) updated and 1 records inserted


--Lets see what we have now --
SELECT prod_code, prod_name 
FROM inventory ORDER BY prod_code;


 prod_code  |         prod_name
------------+---------------------------
 BB1        | Butterboard 174mm x 67mm
 SIR        | Solder Iron
 SIR2       | Solder Iron
 TI_TMS5100 | Texas Instruments TMS5100