Обсуждение: 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.