Besides being easy to schedule and very flexible, manipulating data with queries is extremely powerful and fairly easy to maintain assuming you know a little SQL -- thanks to postgresql's huge array of built in string manipulation functions. Your skills learned here will pay off using the database as well for other things. Not only that, but this approach will be fast since it is declarative and handles entire tables at once as opposed to DTS-ish solutions which tend to do processing record by record. Not to mention they are overcomplicated and tend to suck. (DTS does have the ability to read from any ODBC source which is nice...but that does not apply here).
Different strokes for different folks, it seems. I'd argue that COPY followed by a barrage of plpgsql statements can't be used for anything but the most trivial data migration cases (where it's invaluable) where you have line-organized data input for a hand-full of tables at most. In my experience (which is probably very different from anyone else's), most real world situations include data from a number of very different sources, ranging from the simplest (.csv and, arguably, .xml) to the relatively complex (a couple of proprietary databases, lots of tables, on-the fly row merging, splitting or generating primary keys, date format problems and general pseudo-structured, messed up information). Once you've got your data in your target database (say, pgsql), using SQL to manipulate the data makes sense, but it is only the _final_ step of an average, real world data transformation. Cheers, t.n.a.
