Thread: How do you execute a postgresql function from perl?
I'm trying to execute a postgresql function from perl. The function inserts data into two different tables. I prefer this a sopposed to using two insert statements. Can anyone point me to any information at all on how to execute postgresql functions from Perl? The code for Oracle doesn't work. Any help would be appreciated. Stu Krone skrone@cox.net
On Tue, 8 Apr 2003, Stu Krone wrote: > > I'm trying to execute a postgresql function from perl. The function > inserts data into two different tables. I prefer this a sopposed to > using two insert statements. Can anyone point me to any information at > all on how to execute postgresql functions from Perl? The code for > Oracle doesn't work. Any help would be appreciated. > > Stu Krone > Just submit the SQL for it in the normal manner you use in perl: SELECT domyfunc(); Having been shown some code that runs Oracle procedures recently I think it'll be particularly easy to make a set of functions you can use in perl that emulates that particular oddity and generate the SQL. If I didn't know it was possible to run procedures in Oracle by typing the pl/sql type commands in to the command line utility I'd have said that's all they were doing since it seems much more sensible to have an interface on the backend that understands SQL to do such things rather than SQL and some proprietary language where the proprietary language is necessary in order to use stored procs. -- Nigel J. Andrews
Yeah, I tried that. I even used the same syntax that works for Oracle. The interface said it couldn't handle BEGIN and END in the sql call. I still can't find any syntax that actually executes the function. It's actually pretty surprising to me since executing a stored proc is pretty basic stuff.Thanks for the help anyway! Stu Nigel J. Andrews wrote: > On Tue, 8 Apr 2003, Stu Krone wrote: > > >> I'm trying to execute a postgresql function from perl. The function >>inserts data into two different tables. I prefer this a sopposed to >>using two insert statements. Can anyone point me to any information at >>all on how to execute postgresql functions from Perl? The code for >>Oracle doesn't work. Any help would be appreciated. >> >>Stu Krone >> > > > Just submit the SQL for it in the normal manner you use in perl: > > SELECT domyfunc(); > > Having been shown some code that runs Oracle procedures recently I think it'll > be particularly easy to make a set of functions you can use in perl that > emulates that particular oddity and generate the SQL. If I didn't know it was > possible to run procedures in Oracle by typing the pl/sql type commands in to > the command line utility I'd have said that's all they were doing since it > seems much more sensible to have an interface on the backend that understands > SQL to do such things rather than SQL and some proprietary language where the > proprietary language is necessary in order to use stored procs. > >
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
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 '
DECLAREfile_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
>
Nigel,
Thank you very much for the information. I'm going to try your example.
It's been driving me crazy that I couldn't get it to work, because it's
pretty basic functionality. I'll code it this weekend and let you know
what happens.
Thanks again,
Stu Krone
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
>
On Mon, 14 Apr 2003 16:01:54 +0000, Stu Krone wrote: > 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. > > This means that a function with the correct signature wasn't found - because Postgres allows function overloading, there can be multiple insert_data functions as long as their argument types differ. In this case, the problem is that device_num, inode_num and file_mode are declared as character in the function prototype, but the parameters you are passing to them are numeric. (The reference in the error message to insert_data(unknown,int4,int4,int4) is revealing. Once those constants have been treated as numeric, the signature (character,character,character,character) won't match. ) Solutions: 1. If you change the function to insert_data (text,integer,integer,integer) RETURNS boolean it should work. Or 2. Present the numbers for device_num, inode_num and file_mode inside single quotes if you want them not to be treated as numerics. I hope that helps. Regards John [snipped]
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