Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: copy_options.patch
Description: application/octet-stream (20.5 KB)

Responses

pgsql-hackers by date

Next:From: Shigeru HANADADate: 2012-09-13 09:31:52
Subject: Re: Move postgresql_fdw_validator into dblink
Previous:From: Simon RiggsDate: 2012-09-13 09:00:51
Subject: Re: remove dead ports?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group