From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: COPY Transform support |
Date: | 2008-04-04 01:20:08 |
Message-ID: | 20080404012008.GO6870@frubble.xen.chris-lamb.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Apr 03, 2008 at 03:57:38PM -0400, Tom Lane wrote:
> Gregory Stark <stark(at)enterprisedb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-04-04 01:38:42 | Re: COPY Transform support |
Previous Message | Tom Lane | 2008-04-04 01:04:23 | Re: About numeric division again |