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

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
>