Today's modern web application workflow in its simplest form looks something like this:
Make changes to JSON dataset object and send back to the web server.
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.
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.
I have updated instructions on my gist page for building with PostgreSQL 9.4 Build v8 and plv8
As mentioned in our previous article Building on MingW deploying on VC we often build on MingW and deploy on Windows servers running EDB distributed VC PostgreSQL builds
for extensions we want that don't come packaged. One of the new ones we are really excited about is the PL/V8 and PL/Coffee ones. Could we do it
and would it actually work on a VC build. YES WE CAN and yes it does. I HAZ Coffee and a V8: .
Here are some instructions we hope others will find useful. Even if you aren't on
Windows, you might still find them useful since MingW behaves much like other Unix environments.
As we discussed in file_textarray_fdw Foreign Data Wrapper, Andrew Dunstan's text array foreign data wrapper works great for bringing in a delimited file and not having to worry about the column names until they are in.
We had demonstrated one way to tag the field names to avoid having to keep track of index locations, by using hstore and the header column in conjunction.
The problem with that is it doesn't work for jagged arrays. Jagged arrays are when not all rows have the same number of columns. I've jury rigged a small example
to demonstrate the issue. Luckily with the power of PostgreSQL arrays you can usually get around this issue and still have nice names for your columns. We'll demonstrate that too.
Our new book PostgreSQL: Up and Running is officially out. It's available in hard-copy and e-Book version directly from O'Reilly,
Safari Books Online and available from Amazon in Kindle store. It should be available in hard-copy within the next week or so from other distributors.
Sadly we won't be attending OSCON this year, but there are several PostgreSQL talks going on. If you are speaking at a talk or other PostgreSQL related get together, and would like
to give out some free coupons of our book or get a free e-book copy for yourself to see if it's worth effort mentioning, please send us an e-mail: lr at pcorp.us .
Our main focus in writing the book is demonstrating features that make PostgreSQL uniquely poised for newer kinds of workflows with particular focus on PostgreSQL 9.1 and 9.2.
Part of the reason for this focus is our roots and that we wanted to write a short book to get a feel for the audience. We started to use PostgreSQL in 2001 because of
PostGIS, but were still predominantly SQL Server programmers. At the time SQL Server did not have a spatial component that integrated seamlessly with SQL.
As die-hard SQLers, PostGIS really turned us on. As years went by, we began to use PostgreSQL
not just for our spatial apps, but predominantly non-spatial ones as well that had heavy reporting needs and that we had a choice of platform.
So we came for PostGIS but stayed because of all the other neat features PostgreSQL had that we found lacking in SQL Server. Three off the bat
are arrays, regular expressions, and choice of procedural languages. Most other books on the market just treat PostgreSQL like it's any other relational database.
In a sense that's good because it demonstrates
that using PostgreSQL does not require a steep learning curve if you've used another relational database. We didn't spend as much time on these common features as we'd like to
in the book because it's a short book and we figure most users familiar with relational databases
are quite knowledgeable of common features from other experience. It's true that a lot of people coming to PostgreSQL are looking for cost savings,
ACID compliance, cross-platform support and decent speed
, but as PostgreSQL increases in speed, ease of features, and unique features, we think we'll be seeing more people migrating
just because its simply better than any other databases
for the new kinds of workflows we are seeing today -- e.g. BigData analysis, integration with other datasources, leveraging of domain specific languages in a more seamless way with data.
So what's that creature on the cover? It's an elephant shrew (sengi) and is neither an elephant nor a shrew, but closest in ancestry to the elephant, sea cow, and aardvark.
It is only found
in Africa (mostly East Africa around Kenya) and in zoos. It gets its name from its unusually long nose which it uses for sniffing out insect prey and keeping tabs on its mate. It has some other unusual habits:
it's a trail blazer building trails it uses to scout insect prey and also builds escape routes on the trail it memorizes to escape from predators. It's monogamous, but prefers to keep separate quarters from its mate. Males
will chase off other males and females will chase off other females. It's fast and can usually out-run its predators.