From: | will trillich <will(at)serensoft(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [NOVICE] Perl - Postgres |
Date: | 2003-01-31 22:40:53 |
Message-ID: | 20030131224053.GA1464@mail.serensoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice |
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/ !
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Tkach | 2003-01-31 22:46:04 | Re: very basic question |
Previous Message | Stephane Charette | 2003-01-31 22:38:06 | gp_dump: error in finding the last system oid: ERROR: get_relation_info: Relation 1262 not found |
From | Date | Subject | |
---|---|---|---|
Next Message | Luis Magaña | 2003-01-31 23:13:30 | Name of days and months |
Previous Message | Oliver Elphick | 2003-01-31 22:01:47 | Re: where is libpq.so |