pl/perl problem

RE: [GENERAL] Convert Cursor to array
----- Original Message -----
From: FERREIRA William (COFRAMI)
To: 'pgsql-general@postgresql.org'
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?

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 -----
From: FERREIRA William (COFRAMI) <mailto:william.ferreira@airbus.com>
To: 'pgsql-general@postgresql.org' <mailto:'pgsql-general@postgresql.org'>
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?

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

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

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.

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.