Re: RFD: Don't force plpgsql IN parameters to constant

On Wed, Jul 29, 2009 at 7:55 PM, Steve Prentice<prentice(at)cisco(dot)com> wrote:
> Is there a reason we force plpgsql IN parameters to constant? The reason I
> ask is because having them mutable would go a long way in easing a port from
> Informix's SPL. For better or worse, we have a fair amount of code in SPL
> that does something like:
>
>   -- pObjectId is an IN parameter
>   IF pObjectId IS NULL THEN
>       pObjectId := newid();
>   END IF;
>
> I understand it may be better to use a different technique here, but we have
> a substantial amount of SPL (40k lines) and if we could make the IN
> parameters mutable, it would make my day.
>
> Looking at the history of the code, it looks like this has been the way it
> has been since the beginning. Tom added a comment in 1995 asking why we
> force the IN parameters to constant, but the "why?" part of the comment was
> removed in a later change to support OUT and INOUT parameters.
>
> I've attached a patch that would change this behavior. Also, the test2(int)
> function below works with the patch, but would fail to compile without. I
> also checked to make sure the parameter wasn't passed by reference and it is
> not. The test at the bottom returns 't' meaning test2(int) did not change
> the a variable in test1().
>
> CREATE OR REPLACE FUNCTION test1() RETURNS INT AS $$
> DECLARE
>    a INT;
> BEGIN
>    a := 1;
>    PERFORM test2(a);
>    RETURN a;
> END
> $$ LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION test2(a INT) RETURNS VOID AS $$
> BEGIN
>    a := 2;
> END
> $$ LANGUAGE plpgsql;
>
> SELECT test1() = 1;
>
> If this change would be acceptable, I'll proceed in finishing the patch by
> updating docs and adding regression tests.

Wow. I can imagine about a thousand ways that this could break
existing applications. I would not be prepared to bet a dollar that
anything I've written would survive the impact unscathed.

I have a feeling someone else is going to shoot you out of the water
completely, but all I'll say is it would definitely need to be
OPTIONAL.

...Robert