From: | "Etsuro Fujita" <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | WIP patch: add (PRE|POST)PROCESSOR options to COPY |
Date: | 2012-09-13 09:13:26 |
Message-ID: | 002101cd9190$081c4140$1854c3c0$@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
copy_options.patch | application/octet-stream | 20.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Shigeru HANADA | 2012-09-13 09:31:52 | Re: Move postgresql_fdw_validator into dblink |
Previous Message | Simon Riggs | 2012-09-13 09:00:51 | Re: remove dead ports? |