Adam Mackler <adam@mackler.email> writes:
> Briefly, given the following function:
> CREATE FUNCTION runs(input int[], output int[] DEFAULT '{}')
> RETURNS int[] AS $$
> SELECT
> CASE WHEN cardinality(input) = 0 THEN output
> ELSE runs(input[2:],
> array_append(output, CASE
> WHEN input[1] = 0 THEN 0
> ELSE output[cardinality(output)] + input[1]
> END)
> )
> END
> $$ LANGUAGE SQL;
> I expect the following invocation to return an array with the same number of elements as the passed-in argument
array:
> # select runs('{0,1,1,1,1,0,-1,-1,-1,0}');
> runs
> ----------------------------------------
> {0,1,2,3,4,5,6,0,0,0,-1,-2,-3,-4,-5,0}
> (1 row)
Yeah, there's a bug in here somewhere. If you transpose the logic
into plpgsql, it behaves fine:
CREATE FUNCTION runs_p(input int[], output int[] DEFAULT '{}')
RETURNS int[] AS $$
begin
return
CASE WHEN cardinality(input) = 0 THEN output
ELSE runs_p(input[2:],
array_append(output, CASE
WHEN input[1] = 0 THEN 0
ELSE output[cardinality(output)] + input[1]
END)
)
END;
end
$$ LANGUAGE plpgsql;
so that might do as a workaround. It looks like memory management
in SQL functions is not coping well with expanded arrays, but I'm
not quite sure where it's going off the rails.
regards, tom lane
