Re: Odd behavior in functions w/ anyarray & anyelement - Mailing list pgsql-general

[ slowly catching up on vacation email ]

Joshua Burns <jdburnz@gmail.com> writes:
> From that point I wrote a bunch of simply anyarray/element related
> functions to better understand how these pseudo-types behave, which has
> left me more confused than when I started.

I think you would have been less confused if you'd chosen to write
the test functions as plain SQL functions, that is use this body:
    'select $1' language sql
If you'd done that, there would have been no unexpected conversions.
However, instead you chose to do "return $1" in plpgsql, and what
you forgot about plpgsql is that it will happily try to convert
absolutely anything to absolutely anything else.  It does that by
applying the source type's output function and then the destination
type's input function, and if the input function doesn't spit up,
it declares victory and goes home.  So for instance, in this example:

> CREATE OR REPLACE FUNCTION anyar_anyel(anyarray) RETURNS anyelement AS
> $BODY$
>     BEGIN
>         RETURN $1;
>     END;
> $BODY$ LANGUAGE plpgsql;

> -- Does not work as expected. Should accept TEXT[], should output TEXT[] to
> match input data-type.
> --   Expected: Returns "{one,two}" as TEXT[]
> --   Actual:   Returns "{one,two}" as TEXT
> SELECT anyar_anyel(ARRAY['one', 'two']::TEXT[]);

you do have one conceptual error: anyarray to anyelement is supposed
to return the element type of the input array type.  So when you pass
TEXT[] to this function, the SQL parser decides that the expected
result type is TEXT.  When plpgsql executes this, it has a TEXT[] value
as $1, and instead of blowing up because that isn't TEXT, it coerces
the array to text form and then sees if it can make that string into
TEXT.  Which of course it can.  A SQL function would've blown up, though,
because it doesn't do any magic conversions like that.

> -- Does not work as expected. Should accept INTEGER[], should output
> INTEGER[] to match input data-type.
> --   Expected: Returns "{1,2,3}" as INTEGER[]
> --   Actual:   ERROR: invalid input syntax for integer: "{1,2,3}"
> --             CONTEXT: PL/pgSQL function "anyar_anyel" while casting
> return value to function's return type
> SELECT anyar_anyel(ARRAY[1,2,3]::INTEGER[]);

Here, again, the expected result type is INTEGER, *not* INTEGER[].
plpgsql tries the cast-via-I/O trick, but integer's input function
is not so lax as text's, so it fails, and you get the message shown.

> CREATE OR REPLACE FUNCTION anyel_anyar(anyelement) RETURNS anyarray AS
> $BODY$
>     BEGIN
>         RETURN $1;
>     END;
> $BODY$ LANGUAGE plpgsql;

> -- Does not work as expected. Should accept TEXT[], should output TEXT[].
> --   Expected: Returns "{one,two}" as TEXT[]
> --   Actual:   ERROR: could not find array type for data type text[]
> SELECT anyel_anyar(ARRAY['one', 'two']::TEXT[]);

These examples fail at parse time because we don't have arrays of arrays
(2-D arrays are not that, but something a bit orthogonal).  So the parser
can't identify what the result type ought to be.

            regards, tom lane