Just a heads up, the Second Edition of PostGIS In Action has officially
reached MEAP stage meaning you can buy now and get draft chapters as we
write them and get final copy when released. Have first drafts of 5 chapters so far. And many more coming shortly.
It is currently the Manning Deal of the Day
January 22, 2013
PostGIS in Action
Get half off the MEAP eBook
or MEAP pBook
Enter pgislaunchau in the Promotional Code box when you check out. http://www.manning.com/obe2
With purchase you also get the E-Book copy of the first edition.
In the last article we said you can't have a LEFT JOIN with LATERAL. Turns out we were mistaken and YES indeed you can and when you do it is equivalent or more powerful than SQL Server's OUTER APPLY.
Let us say that in our query we wanted all my zips returned but for ones that had keys where the value is in a certain range, we want those keys returned. we'd do this. The fact we need all even if they have no such keys necessitates us putting the condition in the ON rather than the WHERE.
SELECT zip, (h).key, (h).valueAs val
FROM zcta5 AS foo
LEFT JOIN LATERAL each(hstore(foo)-'zip'::text)As h
One of the improvements coming in PostgreSQL 9.3 is the new LATERAL SQL clause. LATERAL allows you to write more
succinct code than you would be able to otherwise and will be a welcome companion to extensions like hstore and PostGIS which both
have a plethora of set returning functions. In this article, I'll just demonstrate it's use with hstore and subsequent I'll talk
about it's potential use in PostGIS raster,geometry, and topology for exploding subelements.
One thing I was very interested in aside from the succinctness is whether it will be more performant than the older approach.
A perfect test case for hstore would be the example we just demonstrated in Unpivoting data in PostgreSQL. For this example, we'll rewrite the hstore view using LATERAL instead of employing a subselect.