Re: How do you execute a postgresql function from perl? - Mailing list pgsql-hackers

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@postgresql.org so that your
> message can get through to the mailing list cleanly

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