Postgres OnLine Journal     

Now that PostgreSQL 9.3 beta1 has been released we've started to jump start our experimentation by compiling our favorite extensions. First on the list is PL/V8 js.

This was compiled against 9.3beta1 for 64-bit and 32-bit and plv8 version 1.4.0. We briefly tried with the EDB windows builds which we downloaded from: http://www.enterprisedb.com/products-services-training/pgbindownload and seems to work fine.

• PostgreSQL 9.3 plv8 32-bit download
• PostgreSQL 9.3 plv8 64-bit download

We hope windows users find these useful.

We've recompiled our PL/V8 for windows against PostgreSQL 9.2.0 (both 32-bit and 64-bit) with latest PL/V8 code. This includes the additional LiveScript language, which you can use by doing:

New release also includes some bug fixes such as Crash case with non-array in array returning function.

We have also put up binaries and installers for PostGIS 2.0.1 for PostgreSQL 9.2 (32-bit and 64-bit) downloadable from PostGIS website. We haven't deployed the installers to Application StackBuilder yet since we are doing some testing before we release to the masses.

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.

Today's modern web application workflow in its simplest form looks something like this:

1. Get dataset as JSON object usually using yet another JSON query object to pass the request using a javascript framework like JQuery/ExtJS/OpenLayers/Leaflet etc.
2. Make changes to JSON dataset object and send back to the web server.
3. On webserver unravel the JSON object and save to respective database tables. This part is really yucky as it often involves the web application server side language doing the unraveling and then yet another step of setting up stored procedures or other update logic to consume it.

We hate the way people build tiers for the same reason Cartman hates lines at the amusement park. Sure tiers are great for certain things like building connected microcosms, but most of the time they are overkill and if applied too early make your application needlessly complicated. In the end all we care about is data: serving data, analyzing data, getting good data and everything else is just peacock feathers.

The introduction of JSON type support in PostgreSQL 9.2 and languages PL/V8 (PL/Javascript) and its Pythoness-like twin PL/Coffee provides several options for bringing your data and application closer together since they have native support for JSON. In this first part we'll demonstrate one: An upsert stored procedure that takes a single JSON object instead of separate args and updates existing data and adds missing records. In later articles we'll show you the front end app and also add a sprinkle of PostGIS in there to demonstrate working with custom types.