Re: Plperl functions with OUT parameters crashing each other when used in the same connection

Tom Lane wrote:
> "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
>> Here is a reduced example that shows the problem.
>
> Hm, I'm no Perl guru, but isn't the second script to be
> loaded going to redefine those subroutines that the first
> script defined? I'm pretty sure that there's not an implicit
> independent namespace for each plperl function.
>
> regards, tom lane

Hi Tom,

I'm using PGSQL 8.1.4.

I have deleted the subroutines now, but problem remains. Does that mean the variables created inside a plperl function are alive for the duration of the database connection?

------------------------------------------------------------
-- FUNCTION: foo1
------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.foo1
(
IN a integer,
IN b integer,
OUT c integer,
OUT d integer
)
RETURNS SETOF record
AS

$$

@i = ('a', 'b');
@io = ();
@o = ('c', 'd');

$c = 0;
foreach $i (@i) {$input{$i} = @_[$c++]};
foreach $io (@io) {$input{$io} = @_[$c]; $output{$io} = @_[$c++]};
foreach $o (@o) {$output{$o} = @_[$c++]};

$output{'c'} = $input{'a'} + $input{'b'};
$output{'d'} = $input{'a'} * $input{'b'};

return_next \%output;

return undef;

$$

LANGUAGE 'plperl' VOLATILE;

------------------------------------------------------------
-- FUNCTION: foo2
------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.foo2
(
IN n varchar(50),
IN m varchar(50),
OUT r integer,
OUT s varchar(50)
)
RETURNS SETOF record
AS

$$

@i = ('n', 'm');
@io = ();
@o = ('r', 's');

$c = 0;
foreach $i (@i) {$input{$i} = @_[$c++]};
foreach $io (@io) {$input{$io} = @_[$c]; $output{$io} = @_[$c++]};
foreach $o (@o) {$output{$o} = @_[$c++]};

$output{'r'} = $input{'n'} + $input{'m'};
$output{'s'} = $input{'n'} * $input{'m'};

return_next \%output;

return undef;

$$

LANGUAGE 'plperl' VOLATILE;

------------------------------------------------------------
-- FUNCTION TESTS
------------------------------------------------------------
select * from foo1(45,10);
select * from foo2('45','10');

---------------
Philippe Lang
Attik System