Postgres Pro
Downloads
Home   >   mailing lists

Thread: pl/perl problem

pl/perl problem

From
"FERREIRA William (COFRAMI)"
Date:
hi
 
i wrote a store procedure using the pl/perlU language, and the comportment is strange.
my procedure do a select on my database and some traitments too and write the result in a file;
 
when i run the procedure a first time, it works fine, the file is create and data are in. but when i run my procedure a second time, the file is create but the data aren't write in it. where is the problem  ?
 
 
i had an other problem the past week, but i not able to reproduce it. it was a very simple funtion who store a string into a variable and display it on the screen : something like this :
my $toto = '->';
$toto.='titi';
elog NOTICE, $toto;
 
the problem was : the first time i ran the procedure and i get : ->titi and the second time : ->titititi, etc....
the variable $toto wasn't reinitialize. maybe somebody had the same problem.
(if i have enough time, i will post the code)
 
thanks in advance
 
 
    Will

Re: pl/perl problem

From
"Sean Davis"
Date:
 
----- Original Message -----
Sent: Monday, March 21, 2005 9:22 AM
Subject: [GENERAL] pl/perl problem

hi
 
i wrote a store procedure using the pl/perlU language, and the comportment is strange.
my procedure do a select on my database and some traitments too and write the result in a file;
 
when i run the procedure a first time, it works fine, the file is create and data are in. but when i run my procedure a second time, the file is create but the data aren't write in it. where is the problem  ?
 
Can you show the function?
 
 
i had an other problem the past week, but i not able to reproduce it. it was a very simple funtion who store a string into a variable and display it on the screen : something like this :
my $toto = '->';
$toto.='titi';
elog NOTICE, $toto;
 
Again, can you show the whole function?  And do you 'use strict' when coding?

Re: pl/perl problem

From
"FERREIRA William (COFRAMI)"
Date:
my function is very long but i found an example with the same comportment :
CREATE OR REPLACE FUNCTION adoc.totoTest()
  RETURNS int4 AS
$BODY$
 my $var = '->>>';
 &concat($var);

 sub concat {
  $var .= 'tagada';
 }
 elog NOTICE, $var;
 return 4;

$BODY$
  LANGUAGE 'plperl' VOLATILE;
 
first execution : ->>>tagada
second execution : ->>>
 
(for my second problem, i not able to reproduce it....i deleted the source code)
but what means 'use strict' ?
 
thanks
-----Message d'origine-----
De : Sean Davis [mailto:sdavis2@mail.nih.gov]
Envoyé : lundi 21 mars 2005 16:46
À : FERREIRA William (COFRAMI); pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem

 
----- Original Message -----
Sent: Monday, March 21, 2005 9:22 AM
Subject: [GENERAL] pl/perl problem

hi
 
i wrote a store procedure using the pl/perlU language, and the comportment is strange.
my procedure do a select on my database and some traitments too and write the result in a file;
 
when i run the procedure a first time, it works fine, the file is create and data are in. but when i run my procedure a second time, the file is create but the data aren't write in it. where is the problem  ?
 
Can you show the function?
 
 
i had an other problem the past week, but i not able to reproduce it. it was a very simple funtion who store a string into a variable and display it on the screen : something like this :
my $toto = '->';
$toto.='titi';
elog NOTICE, $toto;
 
Again, can you show the whole function?  And do you 'use strict' when coding?

Re: pl/perl problem

From
Richard Huxton
Date:
FERREIRA William (COFRAMI) wrote:
> my function is very long but i found an example with the same comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
>   RETURNS int4 AS
> $BODY$
>  my $var = '->>>';
>  &concat($var);
>
>  sub concat {
>   $var .= 'tagada';
>  }
>  elog NOTICE, $var;
>  return 4;
>
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;
>
> first execution : ->>>tagada
> second execution : ->>>

In the example above $var in sub concat is NOT an argument provided to
the function. What you've done there is create a named closure (if I'm
getting my terms right) in which the inner $var is allocated on first
call but not afterwards. The second time you run totoTest() the outer
$var (my $var) is a new variable, whereas the inner one still refers to
the original.

If you actually want to return a concatenated string you'd want
something like:

sub concat {
  my $var = shift;
  return $var . 'tagada';
}

If you want to affect an outer variable you'll want something like

sub concat {
   my $var_ref = shift;
   $$var_ref .= 'tagada';
}

Does that help?
--
   Richard Huxton
   Archonet Ltd

Re: pl/perl problem

From
Sean Davis
Date:
On Mar 22, 2005, at 3:13 AM, FERREIRA William (COFRAMI) wrote:

> my function is very long but i found an example with the same
> comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
>   RETURNS int4 AS
> $BODY$
>  my $var = '->>>';
>  &concat($var);
>
>
>  sub concat {
>   $var .= 'tagada';
>  }
>  elog NOTICE, $var;
>  return 4;
>
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;
>  
> first execution : ->>>tagada
> second execution : ->>>

Here is a slightly modified version of your code that does what you
want, I think.  A couple of things:

1)  If you want to pass arguments to a subroutine, what you do above
won't work.
2)  You have to be careful in perl when you modify variables that you
know the scope of the variables (where they will be seen versus not)
that you are modifying.
3)  If you want a subroutine to modify the value of a variable passed
to it, you need to pass a REFERENCE to that variable, not the value of
the variable.

CREATE OR REPLACE FUNCTION adoc.totoTest2() RETURNS int4 AS
$BODY$
use strict;             #see below for explanation
my $var = '->>>';
concat(\$var);          #use a reference to the variable
elog NOTICE, $var;
return 4;

sub concat {
   my $ref=shift;        #get a REFERENCE to the variable
   ${$ref} .= 'tagada';  #this dereferences the variable and modifies it
}

$BODY$
LANGUAGE 'plperl' VOLATILE;

>  
> (for my second problem, i not able to reproduce it....i deleted the
> source code)
> but what means 'use strict' ?
>
>

See this article....

http://perl.about.com/od/perlforbeginners/l/aa081701a.htm

Sean

Re: pl/perl problem

From
"FERREIRA William (COFRAMI)"
Date:

thanks a lot
with your example and the example of Richard it works fine

-----Message d'origine-----
De : Sean Davis [mailto:sdavis2@mail.nih.gov]
Envoyé : mardi 22 mars 2005 12:51
À : FERREIRA William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem

On Mar 22, 2005, at 3:13 AM, FERREIRA William (COFRAMI) wrote:

> my function is very long but i found an example with the same
> comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
>   RETURNS int4 AS
> $BODY$
>  my $var = '->>>';
>  &concat($var);
>
>
>  sub concat {
>   $var .= 'tagada';
>  }
>  elog NOTICE, $var;
>  return 4;
>
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;
>  
> first execution : ->>>tagada
> second execution : ->>>

Here is a slightly modified version of your code that does what you
want, I think.  A couple of things:

1)  If you want to pass arguments to a subroutine, what you do above
won't work.
2)  You have to be careful in perl when you modify variables that you
know the scope of the variables (where they will be seen versus not)
that you are modifying.
3)  If you want a subroutine to modify the value of a variable passed
to it, you need to pass a REFERENCE to that variable, not the value of
the variable.

CREATE OR REPLACE FUNCTION adoc.totoTest2() RETURNS int4 AS
$BODY$
use strict;             #see below for explanation
my $var = '->>>';
concat(\$var);          #use a reference to the variable
elog NOTICE, $var;
return 4;

sub concat {
   my $ref=shift;        #get a REFERENCE to the variable
   ${$ref} .= 'tagada';  #this dereferences the variable and modifies it
}

$BODY$
LANGUAGE 'plperl' VOLATILE;

>  
> (for my second problem, i not able to reproduce it....i deleted the
> source code)
> but what means 'use strict' ?
>
>

See this article....

http://perl.about.com/od/perlforbeginners/l/aa081701a.htm

Sean

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

Re: pl/perl problem

From
"FERREIRA William (COFRAMI)"
Date:

yes, it works
exactly what i needed, thanks a lot

-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : mardi 22 mars 2005 12:41
À : FERREIRA William (COFRAMI)
Cc : 'Sean Davis'; pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem

FERREIRA William (COFRAMI) wrote:
> my function is very long but i found an example with the same comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
>   RETURNS int4 AS
> $BODY$
>  my $var = '->>>';
>  &concat($var);
>
>  sub concat {
>   $var .= 'tagada';
>  }
>  elog NOTICE, $var;
>  return 4;
>
> $BODY$
>   LANGUAGE 'plperl' VOLATILE;
> 
> first execution : ->>>tagada
> second execution : ->>>

In the example above $var in sub concat is NOT an argument provided to
the function. What you've done there is create a named closure (if I'm
getting my terms right) in which the inner $var is allocated on first
call but not afterwards. The second time you run totoTest() the outer
$var (my $var) is a new variable, whereas the inner one still refers to
the original.

If you actually want to return a concatenated string you'd want
something like:

sub concat {
  my $var = shift;
  return $var . 'tagada';
}

If you want to affect an outer variable you'll want something like

sub concat {
   my $var_ref = shift;
   $$var_ref .= 'tagada';
}

Does that help?
--
   Richard Huxton
   Archonet Ltd

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.