On Wed, 13 Aug 2008 16:32:18 -0500 ries van Twisk <pg@xxxxxxxxxx> wrote: > On Aug 13, 2008, at 4:25 PM, Ivan Sergio Borgonovo wrote: > > I need to write an import function with enough isolation from > > apache daemon. > > Code has no input other than cvs files and a signal about when to > > start the import. > > The sql code that will be executed will be static. > > I may end up writing a mini-daemon that just start a SQL script > > or just pool from cron and feed psql. > > > > If anyone has a better (lazier, cleaner) approach it will be very > > welcome. > > Lazier would be using JasperETL or any other ETL tool *I did a simple test and \copy works as expected inside transactions.* But I wasn't able to split a \copy command across several lines to improve readability and add comments. Such ETL tools didn't seem suited for my current needs. I downloaded a 190Mb app that has quite a bit of dependencies, tried to run it and gave up the first time, unpacking took too long. I gave a glimpse to the screenshot and I got the impression that at least for me they have a harder learning curve than refreshing my awk knowledge. Out of curiosity I googled for etl postgresql and found kettle. This one was a bit smaller download and I was able to run it immediately. Still it looked more a tool suited for managers that have to produce reports rather than for programmers. They are surely more powerful than a 10 line awk script but I didn't see a quick way to script them. eg. if I had to import 10 tables I'd have to repeat the same click-drag-fill-connect seqence 10 times. I've used MS DTS in SQL 2000 and I've found it has a faster learning curve even if it seems its not as flexible as JasperETL or kettle and I've experienced the same problem of not being able to script it. kettle export an xml file that could be taken as a template for some scripting work. I didn't understand how to execute the resulting plan outside the GUI. I'm wondering if I didn't give them enough time or they are definitively for use case that rarely cross my way. I haven't used dblink in Postgresql yet but I've found the ability to connect to different data sources (including Access and Excel) of those ETL interesting. -- Ivan Sergio Borgonovo http://www.webthatworks.it
