> Hi,
> can anybody address me to online resources to learn how to connect to a
> PostgreSQL DB server through a Perl script?
> Code examples are welcome ;-)
> TIA
>
------------------------- begin perl code -----------------------
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
our $_dbh;
use constant DB_DSN => 'dbi:Pg:dbname=test;';
use constant DB_USER => 'your-user-identity-not-mine';
use constant DB_PASSWORD => 'your-secret-password-not-mine' ;
sub DBH
{
return $_dbh if $_dbh && $_dbh->ping;
for my $tries (1..10) {
$_dbh = DBI->connect( DB_DSN, DB_USER, DB_PASSWORD );
return $_dbh if $_dbh && $_dbh->ping ;
sleep ( $tries * 3 ) ;
}
}
my $sth = DBH->prepare( q {
SELECT CITY.NAME, STATE.NAME
FROM CITY
JOIN STATE
ON CITY.STATE = STATE.ABBREV
WHERE CITY.POPULATION > 50000
} ) or die "Prepare: ", DBH->errstr ;
$sth->execute( )
or die "Execute: ", $sth->errstr ;
while (my ($city, $state) = $sth->fetchrow_array ) {
print "$city is in $state\n";
}
--------------------------- end perl code -------------------------
Of course this entire program is predicated on having a database ...
Here is the one I used in pg_dump format (editted for brevity)
CREATE TABLE state (
name character varying,
abbrev character(2) NOT NULL
);
COPY state (name, abbrev) FROM stdin;
Alabama AL
California CA
Oregon OR
Washington WA
Nevada NV
Idaho ID
Arizona AZ
\.
ALTER TABLE ONLY state
ADD CONSTRAINT state_pkey PRIMARY KEY (abbrev);
CREATE TABLE city (
name character varying,
state character(2),
population integer
);
COPY city (name, state, population) FROM stdin;
San Francisco CA 750000
Phoenix AZ 1200000
Portland OR 540000
Montgomery AL 200000
Birmingham AL 242000
Redding CA 81000
Eugene OR 138000
Olympia WA 42500
Seattle WA 570000
Las Vegas NV 480000
Boise ID 185000
Tuscon AZ 486000
\.
ALTER TABLE ONLY city
ADD CONSTRAINT "$1" FOREIGN KEY (state) REFERENCES state(abbrev);
NB - There are embedded tabs in the COPY sections that your mailer might
destroy.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
