Re: How do you execute a postgresql function from perl?

Hi,

postgresql looks for a function 'insert_data(unknown, int4, int4, int4,)', but
you declare 'insert_data(character, character, character, character)'.
Parametertypes matter.

Try
$rec_ins = $dbh->prepare("select insert_data(?,?,?,?)");
$rv = $rec_ins->execute($file_name . '',
$dev . '',
$ino . '',
$mode . '');

This should force perl to convert the parameters to character-type.

Tommi

Am Samstag, 12. April 2003 11:03 schrieb Stu Krone:
> Hi Nigel,
>
> Nope, no luck.
>
> This is the error I found:
>
> DBD::Pg::st execute failed: ERROR: Function 'insert_data(unknown, int4,
> int4, int4,)' does not exist at ./scope_db_func.pl line 100.
>
>
> I tried single quotes and double quotes.I tried putting select in front
> of the function name and not having it there. Actually the stored proc
> that I've written just does an insert and doesn't even have to return
> anything. This is an abbreviated version of the proc. As you can see,
> there's nothing special here.
>
> CREATE FUNCTION "insert_data" (character,character,character,character)
> RETURNS boolean AS ' DECLARE
> file_name ALIAS FOR $1;
> device_num ALIAS FOR $2;
> inode_num ALIAS FOR $3;
> file_mode ALIAS FOR $4;
> BEGIN
>
> insert into file_system values (max_file_id, file_name,
> device_num,inode_num, file_mode, cur_time, system_user);
>
> insert into file_time (file_id, type_cd, time, assigned_by)
> values (max_file_id, ''atime'', atime, system_user);
>
>
> RETURN FOUND;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> The code to call it is just as simple.
>
>
> use DBI; # required for database access
> my $dbh=
> DBI->connect('DBI:Pg:dbname=cosmo;host=localhost;port=1776','skrone'); my
> $rec_ins = $dbh->prepare("select insert_data(?,?,?,?,?,?,?)");
>
> sub getdata
> {
> my $file_name;
> my $dev;
> my $ino;
> my $mode;
> my $rv;
>
> $inode = stat($_);
> $file_name = $File::Find::name;
> $dev = $inode->dev;
> $ino = $inode->ino;
> $mode = $inode->mode;
>
> $rv = $rec_ins->execute($file_name,
> $dev,
> $ino,
> $mode);
> }
>
>
> find(\&getdata, $input_file_path);
>
>
> $rec_ins->finish;
> $dbh->disconnect;
> exit;
>
> I've striped out most of the functionality of the code, but you can see
> my point from the DB connectivity parts. It still won't work. Everything
> looks reasonable enough, but no luck. I'm sure it has to be possible, I
> just haven't gotten it yet.
>
>
> Stu
>
> On Fri, 2003-04-11 at 15:04, Nigel J. Andrews wrote:
> > I meant to give a perl example.
> >
> > Using DBI:
> >
> > $sth = $dbh->prepare('select myfunc2(?)');
> > $rv = $sth->execute($myvar);
> > print "Number rows = ", $sth->rows, "\n";
> > while ($data = $sth->fetchrow_array) {
> > print "Data: ", $data[0], "\n";
> > }
> > $sth->finish;
> >
> > I can't remember the Pg.pm syntax, I only see that in support mode at the
> > moment so don't really pay much attention to it.
> >
> >
> > --
> > Nigel J. Andrews
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de