Odd behavior in functions w/ anyarray & anyelement

From: Joshua Burns <jdburnz(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Odd behavior in functions w/ anyarray & anyelement
Date: 2013-09-11 20:14:30
Message-ID: CAB73KvQt5jkCg+tmNL_cpE6RpD=Df2Nz2JVOFvUN6fUQc7w6Hw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas 2013-09-11 22:33:01 Re: Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
Previous Message Alex Lai 2013-09-11 20:02:43 Risk of set system wise statement_timeout