Howdy dbi-devers,
More and more lately, I'm writing database functions in PL/pgSQL (in
PostgreSQL) or SQL (in MySQL and others) to do the heavy lifting of
interacting with database tables. I've been thinking that I'd really
love a DBI method to call these functions without having to do the
usual prepare / execute / fetch drill. Even using do() or
fetchrow_array() seems a bit silly in this context:
my ($source_id) = $dbh->fetchrow_array(
'SELECT get_source_id(?)',
undef,
$source,
);
What I'd love is a couple of DBI methods to do this for me. I
recognize that this is currently not defined by the DBI, but I'm
wondering whether it might be time. I've no idea whether JDBC
implements such an interface, but I was thinking of something like
this for function calls:
sub call {
my $dbh = shift;
my $func = shift;
my $places = join ', ', ('?') x @_;
return $dbh->fetchrow_array(
"SELECT $func( $places )",
undef,
@_
);
}
This would allow me to call a function like so:
my $val = $dbh->call('get_source_id', $source );
Which is a much nicer syntax. Drivers might have to modify it, of
course; for MySQL, it should use CALL rather than SELECT.
For functions or procedures that happen to return sets or a cursor,
perhaps we could have a separate method that just returns a statement
handle that's ready to be fetched from?
sub cursor {
my $dbh = shift;
my $func = shift;
my $places = join ', ', ('?') x @_;
my $sth = $dbh->prepare( "SELECT $func( $places )" );
$sth->execute(@_);
return $sth;
}
Just some ideas. I'm sure that there are more complications than this,
but even if we could just have something that handles simple functions
(think last_insert_id() -- eliminate this special case!), I think it'd
go a long way toward not only simplifying the use of database
functions in the DBI, but also toward encouraging DBI users to
actually make more use of database functions.
Thoughts?
Thanks,
David
