Re: [NOVICE] Perl - Postgres - Mailing list pgsql-general

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/ !