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

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 (view raw or flat)
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

pgsql-hackers by date

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

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