Postgres Pro
Downloads
Home   >   mailing lists

Thread: plPerl subroutine

plPerl subroutine

From
"FERREIRA William (COFRAMI)"
Date:
hi
 
is it possible to create subroutines with plPerl ?
 
i tried this :
CREATE OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId int4, evo int4, fileName text, fileRelativeDir text)
  RETURNS int4 AS
$BODY$
 my $theClob='';
 
 my $params = 'select adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;
 $params = $params.')';
 
 $theClob =  spi_exec_query($params);
 elog NOTICE, $theClob;
 
 return 4;
$BODY$
  LANGUAGE 'plperl' VOLATILE;
 
CREATE OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4, evo int4, clob text, indx int4)
  RETURNS text AS
$BODY$
 my $t_clob = $_[3].'totototototototototot';
 
 return $t_clob;
$BODY$
  LANGUAGE 'plperl' VOLATILE;
but the CREATE_XML_FILE doesn't display 'totototototototototot' but HASH(0xf03fa4)....
 
is it possible with this solution or does i need to create a module (including makefile, .pm, ...) ?
if i must create a module, can you explain how to do ?
 
thanks

Re: plPerl subroutine

From
Sean Davis
Date:
My understanding is that pl/perl functions are simply anonymous
coderefs, so they can't call each other.  What is it that you REALLY
want to do?  (The code looks a bit like a toy example.)

Sean

On Mar 16, 2005, at 9:17 AM, FERREIRA William (COFRAMI) wrote:

> hi
>  
> is it possible to create subroutines with plPerl ?
>  
> i tried this :
> CREATE OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId
> int4, evo int4, fileName text, fileRelativeDir text)
>   RETURNS int4 AS
> $BODY$
>  my $theClob='';
>  
>  my $params = 'select
> adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;
>  $params = $params.')';
>  
>  $theClob =  spi_exec_query($params);
>  elog NOTICE, $theClob;
>  
>  return 4;
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;
>  
> CREATE OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4,
> evo int4, clob text, indx int4)
>   RETURNS text AS
> $BODY$
>  my $t_clob = $_[3].'totototototototototot';
>  
>  return $t_clob;
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;
> but the CREATE_XML_FILE doesn't display 'totototototototototot' but
> HASH(0xf03fa4)....
>  
> is it possible with this solution or does i need to create a module
> (including makefile, .pm, ...) ?
> if i must create a module, can you explain how to do ?
>  
>
> thanks

Re: plPerl subroutine

From
Tom Lane
Date:
"FERREIRA William (COFRAMI)" <william.ferreira@airbus.com> writes:
> but the CREATE_XML_FILE doesn't display 'totototototototototot' but
> HASH(0xf03fa4)....

I think what you get back from spi_exec_query is always going to be
a hash, even if it contains only one field.  So you need to pick out
the field value.

            regards, tom lane

Re: plPerl subroutine

From
Harald Fuchs
Date:
In article <1904E3EB39448246A7ECB76DF34A70B00143B48A@TOCOMEXC03>,
"FERREIRA William (COFRAMI)" <william.ferreira@airbus.com> writes:
> CREATE OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId int4, evo
> int4, fileName text, fileRelativeDir text)
>   RETURNS int4 AS
> $BODY$
>  my $theClob='';
>  
>  my $params = 'select
> adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;
>  $params = $params.')';

>  

>  $theClob =  spi_exec_query($params);

>  elog NOTICE, $theClob;

>  

>  return 4;
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;

>  

> CREATE OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4, evo int4,
> clob text, indx int4)
>   RETURNS text AS
> $BODY$
>  my $t_clob = $_[3].'totototototototototot';
>  
>  return $t_clob;
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;

> but the CREATE_XML_FILE doesn't display 'totototototototototot' but
> HASH(0xf03fa4)....


... and rightly so.  As documented in The Fine Manual, spi_exec_query
returns a hash reference.  Probably you want something like

my $rv = spi_exec_query($params);
$theClob = $rv-.{rows}[0]->{get_xml_frag};