Thread: Odd behavior in functions w/ anyarray & anyelement
Greetings,
... With the error: function anyarray_remove(integer[], integer[]) does not exist.
Nothing from what I have read and understand at these URLs document this behavior:
- http://forums.devshed.com/postgresql-help-21/what-s-anyarray-isn-t-it-the-same-as-array-148195.html
- http://www.postgresql.org/message-id/44649BB2.50005@tada.se
- http://www.postgresql.org/docs/9.1/static/extend-type-system.html
Queries Tested On:- http://forums.devshed.com/postgresql-help-21/what-s-anyarray-isn-t-it-the-same-as-array-148195.html
- http://www.postgresql.org/message-id/44649BB2.50005@tada.se
- http://www.postgresql.org/docs/9.1/static/extend-type-system.html
- Windows 2003 R2 (64-Bit), PostgreSQL 9.1.0
- Ubuntu Linux 12.04 LTS (64-bit), PostgreSQL 9.2.4/*============================================================================*/
DROP FUNCTION IF EXISTS anyel_anyel(anyelement);
CREATE OR REPLACE FUNCTION anyel_anyel(anyelement) RETURNS anyelement AS $BODY$
BEGIN
RETURN $1;
END;
$BODY$ LANGUAGE plpgsql;
/*----------------------------------------------------------------------------*/
-- Works as expected. Should accept TEXT, should output TEXT to match input data-type.
-- Expected: Returns "hiho" as TEXT
-- Actual: Returns "hiho" as TEXT
SELECT anyel_anyel('hiho'::TEXT);
-- Works as expected. Should accept INTEGER, should output INTEGER to match input data-type.
--- Expected: Returns "1" as INTEGER
--- Actual: Returns "1" as INTEGER
SELECT anyel_anyel(1::INTEGER);
-- Works 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 anyel_anyel(ARRAY['one', 'two']::TEXT[]);
-- Works as expected. Should accept INTEGER[], should output INTEGER[] to match input data-type.
-- Expected: Returns "{1,2,3}" as INTEGER[]
-- Actual: Returns "{1,2,3}" as INTEGER[]
SELECT anyel_anyel(ARRAY[1,2,3]::INTEGER[]);
/*============================================================================*/
DROP FUNCTION IF EXISTS anyar_anyar(anyarray);
CREATE OR REPLACE FUNCTION anyar_anyar(anyarray) RETURNS anyarray AS $BODY$
BEGIN
RETURN $1;
END;
$BODY$ LANGUAGE plpgsql;
/*----------------------------------------------------------------------------*/
-- Works as expected. Should not accept TEXT because not an array.
-- Expected: ERROR: function anyar_anyar(text) does not exist
-- Actual: ERROR: function anyar_anyar(text) does not exist
SELECT anyar_anyar('hiho'::TEXT);
-- Works as expected. Should not accept INTEGER because not an array.
-- Expected: Throws ERROR: function anyar_anyar(integer) does not exist
-- Actual: Throws ERROR: function anyar_anyar(integer) does not exist
SELECT anyar_anyar(1::INTEGER);
-- Works as expected. Should accept TEXT[], should output TEXT[].
-- Expected: Returns "{one,two}" as TEXT[]
-- Actual: Returns "{one,two}" as TEXT[]
SELECT anyar_anyar(ARRAY['one', 'two']::TEXT[]);
-- Works as expected. Should accept INTEGER[], should output INTEGER[].
-- Expected: Returns "{1,2,3}" as INTEGER[]
-- Actual: Returns "{1,2,3}" as INTEGER[]
SELECT anyar_anyar(ARRAY[1,2,3]::INTEGER[]);
/*============================================================================*/
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;
/*----------------------------------------------------------------------------*/
-- Works as expected. Should not accept TEXT because not an array.
-- Expected: ERROR: function anyar_anyel(text) does not exist
-- Actual: ERROR: function anyar_anyel(text) does not exist
SELECT anyar_anyel('hiho'::TEXT);
-- Works as expected: Should not accept INTEGER because not an array.
-- Expected: ERROR: function anyar_anyel(integer) does not exist
-- Actual: function anyar_anyel(integer) does not exist
SELECT anyar_anyel(1::INTEGER);
-- 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[]);
-- 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[]);
/*============================================================================*/
DROP FUNCTION IF EXISTS anyel_anyar(anyelement);
CREATE OR REPLACE FUNCTION anyel_anyar(anyelement) RETURNS anyarray AS $BODY$
BEGIN
RETURN $1;
END;
$BODY$ LANGUAGE plpgsql;
/*----------------------------------------------------------------------------*/
-- Works as expected: Should accept TEXT, should output TEXT to match input data-type, but should fail because output musdt be array.
-- Expected: ERROR: array value must start with "{" or dimension information
-- Actual: ERROR: array value must start with "{" or dimension information
-- CONTEXT: PL/pgSQL function "anyel_anyar" while casting return value to function's return type
SELECT anyel_anyar('hiho'::TEXT);
-- Works as expected: Should accept INTEGER, should output INTEGER to match input data-type, but should fail because output must be array.
-- Expected: ERROR: array value must start with "{" or dimension information
-- Actual: ERROR: array value must start with "{" or dimension information
-- CONTEXT: PL/pgSQL function "anyel_anyar" while casting return value to function's return type
SELECT anyel_anyar(1::INTEGER);
-- 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[]);
-- Does not work as expected. Should accept INTEGER[], should output TEXT[].
-- Expected: Returns "{1,2,3}" as INTEGER[]
-- Actual: ERROR: could not find array type for data type integer[]
SELECT anyel_anyar(ARRAY[1,2,3]::INTEGER[]);
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.