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.50...@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[]);
