Re: Plperl functions with OUT parameters crashing each other when used in the same connection - Mailing list pgsql-general

pgsql-general-owner@postgresql.org wrote:
> Hi,
>
> I've got a strange bug with two plperl functions using OUT
> parameters: with a fresh ODBC or pgAdmin connection, I can
> call the first function, but then all further calls to the
> second function fail, or call the the second function, but
> then all further calls to the first function fail. Even more
> strange: when the second call fails, the message changes at
> each new try, mentioning one of the variables used as OUT
> parameters in the other function. Something is apprently not
> released from memory between each calls.

Here is a reduced example that shows the problem. Calls at the end work independantly, but the second one called fails
eachtime, mentioning columns from the other function. 

------------------------------------------------------------
--  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');

    &start_sub(@_);

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

    end_sub(@_);

    sub start_sub
    {
        init(@_);
    }

    sub end_sub
    {
        return undef;
    }

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

    sub ret
    {
    while (($key, $value) = each %output) {if (!defined($value)) {elog(ERROR, 'Valeur indéfinie pour ' . $key)}};
    return_next \%output;
    init(@_);
    }

$$

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');

    &start_sub(@_);

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

    end_sub(@_);

    sub start_sub
    {
        init(@_);
    }

    sub end_sub
    {
        return undef;
    }

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

    sub ret
    {
    while (($key, $value) = each %output) {if (!defined($value)) {elog(ERROR, 'Valeur indéfinie pour ' . $key)}};
    return_next \%output;
    init(@_);
    }

$$

LANGUAGE 'plperl' VOLATILE;

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



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