About That Gypsy...

Finally got some time to look at the Postgres version of Northwind, mentioned earlier. Disappointment, but not fatal. Turns out, of the conversions on offer, the PG one is least complete. The file contains only the schema, data, and PK defs. Hmm.

Not only that, but PG has a schizoid relation with letter casing. In the final analysis, unless all your identifiers are lower-case, you'll end having to quote (or escape quote) every bit of SQL you run. Ack. The parser lower-cases anything it sees that doesn't have quotes. So, the DDL can have not only mixed case, but spaces where quotes are used. The file is loaded with all of this. Ack. Now, if you run DML against the table, and get the mixed case correct, but don't quote, you get an object not found error, since the parser insists on lower-casing your text. Irritating. This is, undoubtedly, an artefact of the C/Unix/coders-view-of-the-world heritage of PG. Irritating.

So, out comes vim (SlickEdit version) to the rescue. That gets us the database, schema, data, and PKs.

Fortunately, the FK and view syntax (SQL Server, PG and the MySql versions) are similar enough that grabbing stanzas and editing isn't too irritating. Which leaves the procs. PG doesn't have functions, triggers, and procs as discrete entities, just functions used/labeled also as triggers and procs. Worse, ADS doesn't want to parse proc defs that psql has trouble with. Since procs aren't important, yet, to Northwind's use with R, I'm going to shelve that part of the exercise for now.

Finally, Blogger doesn't allow files for download, and I've not setup a github account; so anyone's who's interested, drop me an e-mail. I'll send along a zip of the three files: base, FKs, and views. The address is hidden in the profile info to the left. Deeply, deeply hidden. You may never find it.