Re: [NOVICE] Perl - Postgres

Rosta Farzan wrote:
> Where can I find the list of the functions of pg module, for
> example how to insert a row to the database? Does anybody
> know a tutorial on perl & Postgres?

man DBI
man DBD::Pg

here's an example on-the-fly, untested, quite likely to burn your
house down and de-neuter your cats:

#!/usr/bin/perl

#use Apache::DBI; # <== if in a mod_perl script
use DBI;

my $DSN = 'niblick';
my $USER= 'frammistat';
my $PSWD= 'plithrod';
# ...or better yet, read them from their own config file.

# for another database engine, change the 'Pg' (and perhaps 'dbname='):
my $dbh = DBI->connect( # DataBase Handle
"dbi:Pg:dbname=$DSN",
$USER,
$PSWD,
);
# dbi:Pg:dbname=$DSN;host=$HOST;port=$PORT ... yada yada

# all the rows at once, and each row in field-order
my $array_of_arrays = $dbh->selectall_arrayref(<<SQL);
SELECT
x.alpha,
x.bravo,
pif.charlie
pif.xray,
pif.yankee,
ww.zulu
FROM
whatever_the_heck ww,
xanadu x,
shazam_goober pif
WHERE
ww.this = pif.that
AND
pix.yada = x.boing
AND
somefn( x.ralph, pix.yoyo ) = someval
SQL
print "record 13 field 7: ",$array_of_arrays->[12][6],"\n";

# updates, inserts and deletes (and creates and drops) all work
# the same way: string together your sql, leaving ? placeholders
# for variable values--
my $sth = $dbh->prepare( # StatemenT Handle
"select * from my_favorite_view where afield = ? or bfield = ?"
);

while (<>) {
chomp;
# naïve de-taint:
next unless /(\w+)/;

# we have two "?" in the prepare above,
# so we have two vals in execute below:
$sth->execute( $1, $1 );

# on each iteration we re-use the $sth->prepare()d sql from
# above, plunking in new values at $sth->execute()
while ( $hashref = $sth->fetchrow_hashref() ) {
print map {"\t",$hashref->{$_}} qw/field list here/;
print "\n";
}

$sth->finish();
}

# and now we're done--
$dbh->disconnect();

__END__

that's the nutshell version. see "man DBI" for more.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !