Odd behavior in functions w/ anyarray & anyelement
Greetings,
I'm trying to track down some undocumented (or perhaps not well documented)
behavior I'm encountering in regards to custom functions (in plpgsql)
utilizing anyelement and anyarray as arguments and/or return types.
I arrived at this point when I was attempting to write the function
"ANYARRAY_REMOVE(anyarray, anyelement)", which returned anyarray.
This function would succeed when calling: SELECT
ANYARRAY_REMOVE(ARRAY[1,2,3], 2)
... But would fail when calling: SELECT ANYARRAY_REMOVE(ARRAY[1,2,3],
ARRAY[1,2])
... With the error: function anyarray_remove(integer[], integer[]) does not
exist.
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.
Here are those functions, queries to interface with those functions, and
what I would expect each query to return or throw vs. what actually happens.
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
- /messages/by-id/44649BB2.50005@tada.se
- http://www.postgresql.org/docs/9.1/static/extend-type-system.html
Queries Tested On:
- 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general