Re: COPY Transform support

On Thu, 03 Apr 2008 16:57:53 +0200, Csaba Nagy <nagy(at)ecircle-ag(dot)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)