Re: Perl, Postgres stored procedure returning refcursor, transaction, etc

Well, I didn't do a great job cleaning that script up. Lines 17, 18,
and 38 are garbage.

---------- Forwarded message ----------

I spent a good deal of time to get the following working after
searching through Google, so I thought maybe there's someone else that
could benefit from this.

The function that is called takes a refcursor as an input parameter
and returns the *same* refcursor as the output. I hardcoded the name
of the refcursor into the statement because I couldn't figure out how
else to get it working...for now it doesn't matter, this works for me,
again, for now. If anyone else has ideas how to avoid this unelegant
hardcoding, please, oh please, let me know.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect(
'dbi:Pg:dbname=database',
'user',
'password',
{ PrintError => 0,
RaiseError => 1,
AutoCommit => 0 } ) ||
die "Could not connect to database: $DBI::errstr";

#$dbh->{TraceLevel} = 1;

my $sql = qq{ SELECT bitemeFunction(?) };
my $sth = $dbh->prepare( $sql );

my $func;

eval {
$func = $dbh->prepare(q{
SELECT xa_get_domain_for_spider_fn( 'biteme' );
FETCH ALL IN biteme;
});

$func->execute;

$dbh->commit;
};

if( $@ ) {
warn "Execution of stored procedure failed: $DBI::errstr\n";
$dbh->rollback;
}

my $count = 0;
my @biteme = ();
while ( @biteme = $func->fetchrow_array ) {
print "$biteme[0]\n";
}
$func->finish;

$dbh->disconnect;

exit;

[Ja nyt se toimii, helkkari soikoon.]
.