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@serensoft.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/ !