Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 588319FB5F2 for ; Mon, 23 Apr 2007 12:10:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 60013-06 for ; Mon, 23 Apr 2007 12:09:50 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 058E99FB769 for ; Mon, 23 Apr 2007 12:09:49 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Mon, 23 Apr 2007 16:56:00 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Mon, 23 Apr 2007 17:09:47 +0200 Message-Id: <462CE84C.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Mon, 23 Apr 2007 17:09:33 +0200 From: "Bart Degryse" To: Subject: plperlu hash problem Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartF3D4943D.0__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.513 tagged_above=0 required=5 tests=AWL=0.001, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_30_40=0.374, HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001, UPPERCASE_25_50=0 X-Spam-Level: X-Archive-Number: 200704/217 X-Sequence-Number: 28217 --=__PartF3D4943D.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit I have a little Perl problem. When I call function dbi_select_test like SELECT * from dbi_select_test() I get the expected result. However when I call SELECT * from dbi_select I get an error message saying "ERROR: error from Perl function: setof-composite-returning Perl function must call return_next with reference to hash at line 9." My perl knowledge seems to be too limited to find the solution myself. Can anyone help me out, please? CREATE OR REPLACE FUNCTION "public"."dbi_select_test" () RETURNS SETOF text AS $body$ use DBI; my $dbh = DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********') or die "Couldn't connect to database: " . DBI->errstr; my $sth = $dbh->prepare('SELECT * FROM address') or die "Couldn't prepare statement: " . $dbh->errstr; $sth->execute() or die "Couldn't execute statement: " . $sth->errstr; while (my $row = $sth->fetchrow_hashref) { return_next($$row{'ADDRTABLEID'}); } $sth->finish; return; $body$ LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE OR REPLACE FUNCTION "public"."dbi_select" () RETURNS SETOF "bmssa"."ADDRESS" AS $body$ use DBI; my $dbh = DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********') or die "Couldn't connect to database: " . DBI->errstr; my $sth = $dbh->prepare('SELECT * FROM address') or die "Couldn't prepare statement: " . $dbh->errstr; $sth->execute() or die "Couldn't execute statement: " . $sth->errstr; while (my %row = $sth->fetchrow_hashref) { return_next($row); } $sth->finish; return; $body$ LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TABLE "bmssa"."ADDRESS" ( "ADDRTABLEID" TEXT, "ADDRRECID" TEXT, "LINENUM" TEXT, "TYPE" TEXT, "NAME" TEXT, "ADDRESS" TEXT, "PHONE" TEXT, "TELEFAX" TEXT, "COUNTRY" TEXT, "DEL_SWIFTNUMBER" TEXT, "ZIPCODE" TEXT, "STATE" TEXT, "COUNTY" TEXT, "TELEX" TEXT, "URL" TEXT, "PHONELOCAL" TEXT, "CELLULARPHONE" TEXT, "EMAIL" TEXT, "TAXGROUP" TEXT, "CITY" TEXT, "STREET" TEXT, "PAGER" TEXT, "SMS" TEXT, "REFZIPCODE" TEXT, "DATAAREAID" TEXT, "RECID" TEXT ) WITHOUT OIDS; --=__PartF3D4943D.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Description: HTML
I have a little Perl problem. When I call function dbi_select_test like SELECT * from dbi_select_test() I get the expected result.
However when I call SELECT * from dbi_select I get an error message saying "ERROR:  error from Perl function: setof-composite-returning Perl function must call return_next with reference to hash at line 9." My perl knowledge seems to be too limited to find the solution myself. Can anyone help me out, please?
 
CREATE OR REPLACE FUNCTION "public"."dbi_select_test" () RETURNS SETOF text AS
$body$
use DBI;
 
my $dbh = DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********')
          or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM address')
          or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
while (my $row = $sth->fetchrow_hashref) {
  return_next($$row{'ADDRTABLEID'});
}
$sth->finish;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
CREATE OR REPLACE FUNCTION "public"."dbi_select" () RETURNS SETOF "bmssa"."ADDRESS" AS
$body$
use DBI;
  my $dbh = DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********')
            or die "Couldn't connect to database: " . DBI->errstr;
  my $sth = $dbh->prepare('SELECT * FROM address')
            or die "Couldn't prepare statement: " . $dbh->errstr;
  $sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
  while (my %row = $sth->fetchrow_hashref) {
    return_next($row);
  }
  $sth->finish;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
CREATE TABLE "bmssa"."ADDRESS" (
  "ADDRTABLEID" TEXT,
  "ADDRRECID" TEXT,
  "LINENUM" TEXT,
  "TYPE" TEXT,
  "NAME" TEXT,
  "ADDRESS" TEXT,
  "PHONE" TEXT,
  "TELEFAX" TEXT,
  "COUNTRY" TEXT,
  "DEL_SWIFTNUMBER" TEXT,
  "ZIPCODE" TEXT,
  "STATE" TEXT,
  "COUNTY" TEXT,
  "TELEX" TEXT,
  "URL" TEXT,
  "PHONELOCAL" TEXT,
  "CELLULARPHONE" TEXT,
  "EMAIL" TEXT,
  "TAXGROUP" TEXT,
  "CITY" TEXT,
  "STREET" TEXT,
  "PAGER" TEXT,
  "SMS" TEXT,
  "REFZIPCODE" TEXT,
  "DATAAREAID" TEXT,
  "RECID" TEXT
) WITHOUT OIDS;
--=__PartF3D4943D.0__=--