Re: COPY Transform support

On Thu, Apr 03, 2008 at 03:57:38PM -0400, Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
> > AFAIK the state of the art is actually to load the data into a table which
> > closely matches the source material, sometimes just columns of text. Then copy
> > it all to another table doing transformations. Not impressed.
> 
> I liked the idea of allowing COPY FROM to act as a table source in a
> larger SELECT or INSERT...SELECT.  Not at all sure what would be
> involved to implement that, but it seems a lot more flexible than
> any other approach.

I'm not sure why new syntax is needed, what's wrong with having a simple
set of procedures like:
readtsv(filename TEXT) AS SETOF RECORD

You'd then be free to do whatever "transformations" you wanted:
 INSERT INTO table (i,j)   SELECT i, MIN(j::INTEGER)   FROM readtsv("file.dat") x(i INTEGER, j TEXT)   WHERE j ~
'^[0-9]+$'  GROUP BY i;
 

You could even have a readlines(filename) procedure that just gives you
back a SETOF TEXT and you can do the parsing yourself.  An associated
regexp split to RECORD would be nice then.

 Sam