Thread: Storing an array to Postgresql table
Hello,
I am a decently proficient perl programmer of many years. I am recently
taking up PostgreSQL in a project involving perl. In my perl script, I
am parsing a multiline "|" delimited "report" and assembling all the
lines for a particular "record" in the report into one long "|"
delimited string that I then split:
@fields=split('\|',$l);
resulting in the array @fields containing the entire "record" in
question (one "field" per array element).
In postgresql, I have created a table whose columns are essentially in
the exact same layout as the array elements (i.e., the table's column1
is logically the equiv to the array's element 0, i.e. $fields[0]).
so, what i am looking for essentially is an equiv to "COPY FROM" except
the "source" is @fields and not a file. and/or some way to the
fetchrow_array method in perl DBI works (but in reverse where i have the
array @fields and i simply want to storerow_array (though I realize
that fetchrow_array is working with a cursor where something like a
storerow_array is more direct)). Is there anything like "tie" for
arrays and DBI in perl like you have for the perl "DB_File" module?
i am planning on using DBI and DBD::Pg. Should I consider PgPerl
instead? Perl is 5.6 on a redhat fedora core 3 server. postgresql is
8.0.3. speed is somewhat a concern as I have (at times) several hundred
thousand "records" from the data source "report" to load and have a
limited amount of time to process on the server each night.
Thanks,
Mark
_______________________________________________________
Mark J. Bailey, CEO Jobsoft Design & Development, Inc.
277 Wilson Pike Circle, Suite 105, Brentwood, TN 37027
EMAIL: mjb@jobsoft.com WEB: http://www.jobsoft.com/
Medimation / CardShot(tm) http://www.medimation.com/
Skylert(tm) -"Stay Alert, Stay Alive!"- www.skylert.com
(615)425-0932x20 FAX:(615)425-0935 CELL:(615)308-9099
On Fri, Jun 17, 2005 at 09:04:32AM -0500, Mark J. Bailey wrote:
>
> so, what i am looking for essentially is an equiv to "COPY FROM" except
> the "source" is @fields and not a file.
You could use COPY FROM STDIN and pg_putline -- you wouldn't even
need to split the record:
$dbh->do("COPY foo (a, b, c, d, e) FROM STDIN WITH DELIMITER '|'");
$dbh->pg_putline("1|2|3|4|5\n");
$dbh->pg_putline("6|7|8|9|10\n");
$dbh->pg_putline("11|12|13|14|15\n");
$dbh->pg_putline("16|17|18|19|20\n");
$dbh->pg_endcopy;
See "COPY support" in the DBD::Pg documentation for more info:
http://search.cpan.org/~dbdpg/DBD-Pg-1.42/Pg.pm#COPY_support
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Fri, Jun 17, 2005 at 09:04:32 -0500, "Mark J. Bailey" <mjb@jobsoft.com> wrote: > > so, what i am looking for essentially is an equiv to "COPY FROM" except > the "source" is @fields and not a file. and/or some way to the > fetchrow_array method in perl DBI works (but in reverse where i have the > array @fields and i simply want to storerow_array (though I realize > that fetchrow_array is working with a cursor where something like a > storerow_array is more direct)). Is there anything like "tie" for > arrays and DBI in perl like you have for the perl "DB_File" module? > > i am planning on using DBI and DBD::Pg. Should I consider PgPerl > instead? Perl is 5.6 on a redhat fedora core 3 server. postgresql is > 8.0.3. speed is somewhat a concern as I have (at times) several hundred > thousand "records" from the data source "report" to load and have a > limited amount of time to process on the server each night. You can copy from stdin and send the data from your program. I have done this using Pg (which I think is what you are calling PgPerl). The DBD::Pg man page mentions being able to do this as well, though I haven't tried it.