WIP patch: add (PRE|POST)PROCESSOR options to COPY

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

Responses

Browse pgsql-hackers by date

  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?