PLV8JS and PLCoffee Part 2: JSON search requests

PostgreSQL 9.2 beta3 got released this week and so we inch ever closer to final in another 2 months or so. One of the great new features is the built-in JSON type and companion PLV8/PLCoffee languages that allow for easy processing of JSON objects. One of the use cases we had in mind is to take as input a JSON search request that in turn returns a JSON dataset.

We'll use our table from PLV8 and PLCoffee Upserting. Keep in mind that in practice the json search request would be generated by a client side javascript API such as our favorite JQuery, but for quick prototyping, we'll generate the request in the database with some SQL.

If you are on windows and don't have plv8 available we have PostgreSQL 9.2 64-bit and 32-bit plv8/plcoffee experimental binaries and instructions. We haven't recompiled against 9.2beta3, but our existing binaries seem to work fine on our beta3 install.

Basic search stored function

This is a very basic search that assumes certain arguments of search term, page and offset are passed in the request.

-- PL/Javascript version
CREATE OR REPLACE FUNCTION simple_search_inventory(param_search json)
  RETURNS json AS
$$
o = JSON.parse(param_search);
/** Take a json search request and output a json dataset **/
rs = plv8.execute("SELECT prod_code, prod_name " + 
" FROM inventory  " + 
" WHERE prod_name ILIKE $1 LIMIT $2 OFFSET($3 - 1)*$2"
    , [o.prod_name_search, o.num_per_page, o.page_num]);
return JSON.stringify(rs);

$$
  LANGUAGE plv8 VOLATILE;



-- PL/Coffeescript version looks about the same except different comment tag and language specified
CREATE OR REPLACE FUNCTION simple_search_inventory(param_search json)
  RETURNS json AS
$$
o = JSON.parse(param_search)
## Take a json search request and output a json dataset 
rs = plv8.execute("SELECT prod_code, prod_name " + 
" FROM inventory  " + 
" WHERE prod_name ILIKE $1 LIMIT $2 OFFSET ($3 - 1)*$2"
    , [o.prod_name_search, o.num_per_page, o.page_num])
return JSON.stringify(rs)
$$
  LANGUAGE plcoffee VOLATILE;


To test the stored function, we'd write a query something like:
SELECT simple_search_inventory('{"prod_name_search":"%I%"
	, "num_per_page":2
	, "page_num":1}'::json);
	
Which would output something like this:
-- output --
[{"prod_code":"SIR","prod_name":"Solder Iron"},{"prod_code":"TI_TMS5100","prod_name":"Texas Instruments TMS5100"}]