Re: COPY Transform support

On Thu, 03 Apr 2008 16:57:53 +0200, Csaba Nagy <nagy@ecircle-ag.com> wrote:

> On Thu, 2008-04-03 at 16:44 +0200, PFC wrote:
>> CREATE FLATFILE READER mydump (
>>     id    INTEGER,
>>     date    TEXT,
>>     ...
>> ) FROM file 'dump.txt'
>> (followed by delimiter specification syntax identical to COPY, etc)
>> ;
>
> Very cool idea, but why would you need to create a reader object
> first ? You should be able to use COPY directly with the target table
> being omitted,  meaning the copy will not pump it's result in the target
> but be equivalent to a select... and use it in any place where a select
> can be used. This would have absolutely no new  syntax, just the rules
> changed...
>
> Now that I had a second look you actually need the field definitions to
> meaningfully interpret the file,
Yeah, you need to tell Postgres the field names, types, and NULLness  
before it can parse them... or else it's just a plain flat text file which  
makes no sense...
> but then why not use a record
> specification instead of the table in the normal COPY command ? I'm not
> sure if there's any existing syntax for that but I would guess yes...
Hm, yeah, that's even simpler, just create a type for the row (or just  
use table%ROWTYPE if you have a table that fits the description), and tell  
COPY to parse according to the row type definition... smart...
Like :

CREATE TYPE import_rowtype AS (id INTEGER, date TEXT);
INSERT INTO mytable (id, date, ...)  SELECT id, NULLIF( date, '0000-00-00' )::DATE  FROM (COPY AS import_rowtype FROM
'mysql_trash.txt')AS foo  WHERE (FKs check and drop the borken records);
 
Looks clean...
Obviously, in this case (and also in my proposal's case) you must use  
COPY and not \copy since it is the database server which will be reading  
the file.This could probably be hacked so the client sends the file via the \copy  
interface, too...

> In any case, such a feature would help a lot in processing input files
> based also on other existing data in the DB.
Yeah, it would be cool.Also, since COPY TO can use a SELECT as a data source, you could use  
postgres to read from a file/pipe, process data, and write to a file/pipe  
(kinda better than sed, lol)