Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BE77E9FB79F for ; Thu, 26 Apr 2007 04:07:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 67694-07 for ; Thu, 26 Apr 2007 04:07:24 -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 C99289FB4CE for ; Thu, 26 Apr 2007 04:07:25 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Thu, 26 Apr 2007 08:53:32 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Thu, 26 Apr 2007 09:07:23 +0200 Message-Id: <46306BC0.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Thu, 26 Apr 2007 09:07:13 +0200 From: "Bart Degryse" To: Subject: plperlu hash problem Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartE0C79BB1.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.145 tagged_above=0 required=5 tests=AWL=0.374, BAYES_20=-0.74, 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/258 X-Sequence-Number: 28258 --=__PartE0C79BB1.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 or refer me to a better suited forum? Thanks 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; --=__PartE0C79BB1.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 or refer me to a better suited forum? Thanks
 
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;
--=__PartE0C79BB1.0__=--