COPY Transform support

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: COPY Transform support
Date: 2008-04-03 13:17:23
Message-ID: 200804031517.29357.dfontaine@hi-media.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Here's a proposal for COPY to support the T part of an ETL, that is adding the
capability for COPY FROM to Transform the data it gets.

The idea is quite simple: adding to COPY FROM the option to run a function on
the data before to call datatype_in functions. This needs some syntax
addition to be worked out at the COPY side, then the COPY code will have to
run the given function on the read data and consider giving the output of it
to current COPY code (datatype input function).

The function could either get the data as text or bytea, and would have to
return either text or bytea. bytea seems the more sensible choice, as long as
we don't lose encoding information there, which I'm not sure about.

The syntax could be something like:
COPY mytable FROM '/my/file.txt' WITH COLUMN x CONVERT USING myfunc;

I tried to only add keywords already present in [1], while getting something
meaningfull... and x is intended to be the column number, counting from 1.
[1] http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html

Comments?
--
dim

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Sabino Mullane 2008-04-03 13:54:11 Re: modules
Previous Message Dawid Kuroczko 2008-04-03 12:44:53 Re: psql \G command -- send query and output using extended format