From: | Dimitri Fontaine <dfontaine(at)hi-media(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Andrew Dunstan <andrew(at)dunslane(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: COPY IN as SELECT target |
Date: | 2009-12-17 19:45:36 |
Message-ID: | 5722892F-DBA6-4E5C-8844-C83E45375A94@hi-media.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
Le 17 déc. 2009 à 19:39, Josh Berkus a écrit :
> Mind you, returning (arbitrary expression) would be even better, but if
> we can get returning TEXT[] for 8.5, I think it's worth doing on its own.
Well, you already have it as soon as you have text[]:
INSERT INTO destination
SELECT row[0], row[1], myfunction(row[0], row[1]), row[2]::int + 1
FROM (COPY RETURNING text[] FROM '/path/to/file.cvs' CVS HEADER) as file(row);
Of course as Andrew said already what it needs that the syntax here does not cover is ragged file processing, that is accepting file content when all the rows will not have the same number of columns.
But if you have ragged input reading and COPY as a relation in a query, then you're able to apply any expression you want to in the query itself. Such as transforming the input slightly in order to conform to PostgreSQL datatype input syntaxes, e.g.
Regards,
--
dim
Let's deprecate pgloader.
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2009-12-17 19:53:27 | Re: determine snapshot after obtaining locks for first statement |
Previous Message | Robert Haas | 2009-12-17 19:17:52 | Re: COPY IN as SELECT target |