Обсуждение: Odd behavior in functions w/ anyarray & anyelement

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

Two possible interpretations:

1) must return an array of whatever type is supplied; this is (apparently)
the defined behavior
2) must return an array whose base type is the same as the base type of the
input

The first interpretation seems the most useful.  Your last two examples for
this function should indeed fail.

That said I am not really sure why they have to.  In theory "RETURN
anyarray", if paired with an array anyelement, could output/require an array
with one additional dimension compared to the input.  In your example you
should expect something like:

3) ARRAY[ARRAY['one','two']::text[]]::text[][]

I guess this could be considered a third interpretation....

So the bigger question is: should PostgreSQL really care?  Option #2 then
makes the fewest assumptions: the base types must match AND the output must
be some form of array.

And the biggest question is whether there are use-cases for the more
complex/flexible behavior so that someone may be enticed to implement it -
and consider the backward compatibility concerns.

Regardless, hopefully this aids your understanding.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Odd-behavior-in-functions-w-anyarray-anyelement-tp5770537p5770555.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Joshua Burns wrote
> DROP FUNCTION IF EXISTS anyar_anyel(anyarray);
> CREATE OR REPLACE FUNCTION anyar_anyel(anyarray) RETURNS anyelement AS
> $BODY$
>     BEGIN
>         RETURN $1;
>     END;
> $BODY$ LANGUAGE plpgsql;

Similar to my comment on anyelement->anyarray:

The original goal here is to reduce dimensionality.  In the simple case an
array with a base type is required as input and the output is a scalar
having the same base type.

For both of these the most common usage is to go between 0-dim. and 1-dim.

In theory this limited behavior should probably be restricted to
"anynonarray->anyarray" and vice-versa while the anyelement variations can
allow the more permissive/flexible check of identical base types.

Again, hopefully this is educational.  I'm going from the described behavior
and my general understanding of how things work.  I am not familiar with the
source code nor the design considerations that preceded its creation.

I'm not sure any real conclusions/goals can be drawn absent something more
specific than example queries.  The behavior described (up/down-grading
between 1-dim arrays and scalar values) explains away all your "unexpected"
results.  But your expectations are not unreasonable - just not commonly
used/needed in practice; or if they are someone else has a
solution/work-around I am not familiar with.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Odd-behavior-in-functions-w-anyarray-anyelement-tp5770537p5770556.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[ 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

Tom Lane-2 wrote
> 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.

While this is how it behaves in practice I did not find this described in
the documentation.

There are three relevant psuedo-types in play for this:

anyelement
anyarray
anynonarray

Since the documentation states that anyelement can take on array types the
OP deduced that these signatures:

func(anyelement) returns anyarray
func(anyarray) returns anyelement --including arrays...

if provided array input would be able to echo out the same unmodified array.

if you truly want to enforce an up/down-grading function you would write:

func(anynonarray) returns anyarray
func(anyarray) returns anynonarray

I don't see how the behavior can reasonably change at this point but a
second opinion on the current documentation wouldn't hurt.

http://www.postgresql.org/docs/9.3/interactive/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Odd-behavior-in-functions-w-anyarray-anyelement-tp5770537p5777628.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> Tom Lane-2 wrote
>> 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.

> While this is how it behaves in practice I did not find this described in
> the documentation.

No?  What I read in
http://www.postgresql.org/docs/9.3/static/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC
is:

  Polymorphic arguments and results are tied to each other and are resolved
  to a specific data type when a query calling a polymorphic function is
  parsed. Each position (either argument or return value) declared as
  anyelement is allowed to have any specific actual data type, but in any
  given call they must all be the same actual type. Each position declared
  as anyarray can have any array data type, but similarly they must all be
  the same type. And similarly, positions declared as anyrange must all be
  the same range type. Furthermore, if there are positions declared anyarray
  and others declared anyelement, the actual array type in the anyarray
  positions must be an array whose elements are the same type appearing in
  the anyelement positions.

The last sentence is what I was saying, no?

You can if you like replace "anyelement" by "anynonarray", but that won't
change the semantics if there's also an occurrence of "anyarray", because
that's going to constrain the anyelement type to be something that has an
associated array type.  (If we had arrays of arrays, then these two cases
might differ ... but we don't.)

FWIW, the original design for polymorphic functions didn't have
anynonarray, and we didn't particularly need it.  My recollection is
that it's basically a kludge that we invented later to allow the text
concatenation and array concatenation versions of "||" to coexist.

            regards, tom lane

Tom Lane-2 wrote
>   Furthermore, if there are positions declared anyarray
>   and others declared anyelement, the actual array type in the anyarray
>   positions must be an array whose elements are the same type appearing in
>   the anyelement positions.
>
> The last sentence is what I was saying, no?

You are correct.  My only remaining concern then is one of style as opposed
to substance.

David J.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Odd-behavior-in-functions-w-anyarray-anyelement-tp5770537p5777634.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.