I'd like to add the following options to the SQL COPY command and the psql \copy
instruction:
* PREPROCESSOR: Specifies the user-supplied program for COPY IN. The data
from an input file is preprocessed by the program before the data is loaded into
a postgres table. * POSTPROCESSOR: Specifies the user-supplied program for COPY OUT. The data
from a postgres table is postprocessed by the program before the data is stored
in an output file.
These options can be specified only when an input or output file is specified.
These options allow to move data between postgres tables and e.g., compressed
files or files on a distributed file system such as Hadoop HDFS. Former
examples are shown below:
$ echo '/bin/gunzip -c $1' > decompress.sh
$ chmod +x decompress.sh
postgres=# COPY foo FROM '/home/pgsql/foo.csv.gz' WITH (format 'csv',
preprocessor '/home/pgsql/decompress.sh');
$ echo '/bin/gzip > $1' > compress.sh
$ chmod +x compress.sh
postgres=# COPY bar TO '/home/pgsql/bar.csv.gz' WITH (format 'csv',
postprocessor '/home/pgsql/compress.sh');
Attached is a WIP patch. Comments and questions are welcome.
(By using these options, I think it's also possible to develop a variant of
file_fdw, for example a compressed file wrapper and Hadoop HDFS wrapper.)
Thanks,
Best regards,
Etsuro Fujita
