I created a new shell-type utility for Pg that i think does some pretty interesting things: essentially it is like pg_loader, except it does not require any configuration files. Instead it relies on COPY and pseudo-COPY syntax. What it does is add two new "psedo-classes" of directives: `-PERL_key [ = value]`; and, `-TCSV_key [ = value]` The first of these `-TCSV_key [ = value]`, brings the full power of Text::CSV_XS. All of the keys here correspond to that of Text::CSV_XS - which becomes a csv-preprocessor. This permits loose-quotes, true functionality with CSV headers, and making empty quotes null amongst other things. The second `-PERL_key [ = value ]`, sets a special hash which permits you do custom things like `input_order`ing on either the positions of the columns in the csv `\3 \2 \1`, or on the header of the csvs `year,make,model` (which by proxy also permits ragged csvs).. It also adds `header_lowercase`, so you don't have to worry about the case of the header in other commands that use it. It further adds `ragged` where the csv is truncated to the amount of destination columns in the COPY. Many other clauses map transparently into Text::CSV syntax: DELIMITER, NULL AS, ESCAPE, etc. Using the utility is rather simple, it can read from STDIN: echo \COPY foo FROM test.txt WITH DELIMITER AS '~' CSV HEADER -PERL_ragged -TCSV_empty_is_undef | \ bdshell -U username -d database ; Or, it can be run in some type of batch command: bdshell -U username -d database -c "\COPY foo FROM test.txt WITH DELIMITER AS '~' CSV HEADER -PERL_input_order '\3 \2 \1' You can find the code here: http://github.com/EvanCarroll/pgperlshell/blob/master/bdshell I use the new perl6-esque Regexp::Grammars to parse COPY, I think as I write grammars for other SQL statements I'll start to expand it to be more shellish. -- Evan Carroll System Lord of the Internets http://www.evancarroll.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
