Odd behavior in functions w/ anyarray & anyelement

[Joshua Burns <jdburnz@xxxxxxxxx>]

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
- http://www.postgresql.org/message-id/44649BB2.50005@xxxxxxx
- 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[]);