contrib spotlight   

We discussed a while back the Text array foreign data wrapper that allows you to register and query a delimited file as if it were a regular table with one array column. It's probably the FDW we use most often and reminded of that recently when I had to query a 500,000 record resident list tab delimited file to prep for geocoding.

When we upgraded to 9.2 and we could no longer compile, I wrote to Andrew Dunstan about this and he kindly created a 9.2 version. Unfortunately there are still quite a few FDWs broken as a result of the 9.2 changes and I was hoping to try to apply similar patches to them that I saw Andrew do, but haven't had the patience or time yet. Anyway we've compiled these for 9.2 under our mingw64-w64 and mingw64-w32 chains using Andrew's 9.2 GitHub stable branch. https://github.com/adunstan/file_text_array_fdw/tree/REL9_2_STABLE and we've tested them using the PostgreSQL EDB windows VC++ compiled versions. We hope you find them as useful as we have.

• PostgreSQL 9.2 w32 FDWs
• PostgreSQL 9.2 w64 FDWs

I'm hoping to add more FDWs to these 9.2 bags once we have those working again. If you want to compile yourself or compile others, we have instructions in the packaged README.txt.

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.

A while ago we demonstrated how to create cross tabulation tables using tablefunc extension aka (Pivot Tables) (basically collapsing rows into columns). Recently someone asked me how to do the reverse (convert columns to rows). He found a solution to the problem here: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql using a combination of array and unnest. That approach is very similar to SQL Server's built-in Pivot SQL predicate. The solution seemed nice enough except similar to the SQL Server Unpivot, it required knowing the column names beforehand so very hard to genericize. So would it be possible to accomplish this feat without knowing the columns names (except for the key) and be able to do it with one SQL statement. I realized that the PostgreSQL hstore extension fit the bill nicely. In this article I'll demonstrate both approaches by creating a view using both.