Date: Tue, 16 Mar 2004 23:04:00 +0900 (JST)
Message-Id: <20040316.230400.39151407.t-ishii@sra.co.jp>
To: postgresql@finner.de
Cc: noor@cs.man.ac.uk, pgsql-sql@postgresql.org
Subject: Re: flat file database to postgres
From: Tatsuo Ishii <t-ishii@sra.co.jp>
In-Reply-To: <20040316143023.29ad9dbd.postgresql@finner.de>
References: <4133.80.46.172.2.1079440437.squirrel@popeye.cs.man.ac.uk>
	<20040316143023.29ad9dbd.postgresql@finner.de>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

> On Tue, 16 Mar 2004 12:33:57 -0000 (GMT) noor@cs.man.ac.uk sat down, thou=
ght long and then wrote:
>=20
> >=20
> > Hi
> >=20
> > Does anybody know how to export data from a flatfile (using perl) datab=
ase
> > to postgres?
> >=20
> > Thanks
>=20
> If it is some kind of CSV (text with separators) you could use DBD::CSV f=
or reading and DBD::Pg for
> writing. Or you read the file with Perl=B4s standard functions, treat the=
 data to be
> PostgreSQL-insert-query-compatible (regarding apostrophes for example) an=
d then use DBD::Pg (that=B4s
> what I usually do). If you have some kind of DBase files, you can use DBD=
::XBase.

I wrote a user defined C function which read a file and returns it.

test=3D# SELECT * FROM exttable('/etc/group', ':') AS g(gname TEXT, dummy T=
EXT, gid INTEGER, gmembers TEXT) LIMIT 10;
 gname  | dummy | gid |    gmembers=20=20=20=20=20
--------+-------+-----+-----------------
 root   | x     |   0 | root
 bin    | x     |   1 | root,bin,daemon
 daemon | x     |   2 | root,bin,daemon
 sys    | x     |   3 | root,bin,adm
 adm    | x     |   4 | root,adm,daemon
 tty    | x     |   5 |=20
 disk   | x     |   6 | root
 lp     | x     |   7 | daemon,lp
 mem    | x     |   8 |=20
 kmem   | x     |   9 |=20
(10 rows)

Of course this kind of function could be dangerous if you
thoughtlessly use it. Anyway I could post it if there's enough
interest.
--
Tatsuo Ishii