
	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


Tue, 08 Apr 2003 22:29:17 -0700
Stu Krone <skrone@blueonyxgroup.com>
How do you execute a postgresql function from perl?

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


Wed, 9 Apr 2003 17:37:27 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: How do you execute a postgresql function from perl?
	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.
> 
> 


Wed, 09 Apr 2003 09:45:55 -0700
Stu Krone <skrone@blueonyxgroup.com>
Re: How do you execute a postgresql function from perl?


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


Fri, 11 Apr 2003 23:04:46 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: How do you execute a postgresql function from perl?
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
> 


11 Apr 2003 15:12:25 -0700
Stu Krone <skrone@blueonyxgroup.com>
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
> 


12 Apr 2003 02:03:55 -0700
Stu Krone <skrone@blueonyxgroup.com>
Re: How do you execute a postgresql function from perl?
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]


Mon, 14 Apr 2003 17:52:34 +0100
"John Gray" <jgray@azuli.co.uk>
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@postgresql.org so that your
> message can get through to the mailing list cleanly

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


Wed, 16 Apr 2003 09:45:20 +0200
Tommi Maekitalo <t.maekitalo@epgmbh.de>
Re: How do you execute a postgresql function from perl?