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

pgsql-general-owner@postgresql.org wrote:

> Tom Lane wrote:
>> "Philippe Lang" <philippe.lang@attiksystem.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?

It seems to be the case: if I rename all the variables in foo2 function, I do not have anymore problems.

Is there a way to "flush" all the variables explicitely?


------------------------------------------------------------
--  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

$$

    @i2 = ('n', 'm');
    @io2 = ();
    @o2 = ('r', 's');

    $c2 = 0;
    foreach $i2 (@i2) {$input2{$i2} = @_[$c2++]};
    foreach $io2 (@io2) {$input2{$io2} = @_[$c2]; $output2{$io2} = @_[$c2++]};
    foreach $o2 (@o2) {$output2{$o2} = @_[$c2++]};

    $output2{'r'} = $input2{'n'} + $input2{'m'};
    $output2{'s'} = $input2{'n'} * $input2{'m'};

    return_next \%output2;

    return undef;

$$

LANGUAGE 'plperl' VOLATILE;


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