On Thu, 03 Apr 2008 16:57:53 +0200, Csaba Nagy <[EMAIL PROTECTED]> 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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
