Re: perl and psql

Yes you can and in a very simple manner. I'll sketch the outline and leave you to fill in the details as it's a very useful learning exercise.

Instead of printing, call a SQL command to insert them into a table. That is, use something like something like:

$dbh->foreach my $cluster (@clusters) {
do ("INSERT INTO $table (start, end) VALUES (cluster[0], cluster[1]);"
+);
}

#  Ensure that the insertion is committed (or set auto-commit when fir
+st connecting to the DB).
$dbh->commit();
[download]

This presumes you have created a psql table, the name of which is held in $table, with columns called 'start' and 'end'. Creating that table is left as an easy exercise.

Paul

Comment on Re: perl and psql Download Code

Replies are listed 'Best First'.
Re^2: perl and psql by almut (Canon) on Dec 02, 2009 at 14:18 UTC
`$dbh->do ("INSERT INTO $table (start, end) VALUES (cluster[0], cluster[1]);");` In general, it's a good idea to use placeholders (like the OP already did in the SELECT statement), because that way you avoid potential quoting issues, and don't run the risk of SQL injections... `foreach my $cluster (@clusters) { my $sth = $dbh->prepare("INSERT INTO $table (start, end) VALUES (? +, ?)") or die $dbh->errstr; $sth->execute($cluster->[0], $cluster->[1]) or die $sth->errstr;; }` [download] (assuming the interpolated variable `$table` is "safe", i.e. initialised from a static name stored program-internally...)	[reply] [d/l] [select]
Re^2: perl and psql by gogoglou (Beadle) on Dec 02, 2009 at 14:14 UTC
thanks for your reply. I am really new in perl soany help is usefull. I get a small error message though. first I create at he top of the script my $table which has a start and an end position, but then when I replace the print with the example you gave me I get an error that says that my clusters requires explicit package name. I don't really understand why this happens in this case, since the clusters have been declared. Any ideas would be really usefull. Thanks	[reply]
Re^3: perl and psql by bv (Friar) on Dec 02, 2009 at 14:29 UTC
There was a bug in the code that was posted as a reply. I don't think Xilman intended that code to be blindly copy-and-pasted, but here's what's probably going on: # This should not have the '$dbh->' at the beginning, but the real iss +ue is # $cluster here is a scalar, but foreach my $cluster (@clusters) { # here, they are being treated as arrays (if they were interpolated as + written. # $dbh->do ("INSERT INTO $table (start, end) VALUES (cluster[0], clust +er[1]);"); # a better approach, if your DBI module supports placeholders: $dbh->do ("INSERT INTO $table (start, end) VALUES (?,?);", @{$cluster} +) # or even: $dbh->do ("INSERT INTO $table (start, end) VALUES (?,?);", +$cluster->[0], $cluster->[1]) } [download] Again, this is untested code, and may not work as advertised. If the `->` and `@{}` are confusing you, check out perlreftut for more info on references. `@_=qw; Just another Perl hacker,; ;$_=q=print "@_"= and eval;` [download]	[reply] [d/l] [select]
Re^4: perl and psql by almut (Canon) on Dec 02, 2009 at 15:59 UTC
`$dbh->do ("INSERT INTO $table (start, end) VALUES (?,?);", @{$cluster})` When you use do with placeholders and bind values, you also need to specify the `\%attr` hashref (for syntactical reasons, because the synopsis is `$dbh->do($statement, \%attr, @bind_values)`). You may use `undef` if you don't have any attributes. `$dbh->do ("INSERT INTO $table (start, end) VALUES (?,?)", undef, @$clu +ster)` [download]	[reply] [d/l] [select]
Re^4: perl and psql by Xilman (Hermit) on Dec 02, 2009 at 17:11 UTC
Thank you. It was an unfortunate cut&paste error on my part. I'm well aware, of course, that place holders are a Good Idea(tm). The original Perl script from which I made the c&p used them but I removed them from the posted snippet to simplify it. As I said originally, the OP should take my fragment as a hint for a learning exercise and not as a complete answer. Paul	[reply]