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