Home > mailing lists

Odd behavior in functions w/ anyarray & anyelement - Mailing list pgsql-general

From	Joshua Burns
Subject	Odd behavior in functions w/ anyarray & anyelement
Date	September 12, 2013 02:21:36
Msg-id	CAB73KvQt5jkCg+tmNL_cpE6RpD=Df2Nz2JVOFvUN6fUQc7w6Hw@mail.gmail.com Whole thread Raw
Responses	Re: Odd behavior in functions w/ anyarray & anyelement Re: Odd behavior in functions w/ anyarray & anyelement Re: Odd behavior in functions w/ anyarray & anyelement
List	pgsql-general

Tree view

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

pgsql-general by date:

From: David Johnston
Date: 12 September 2013, 01:52:43
Subject: Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

From: Alex Lai
Date: 12 September 2013, 02:21:38
Subject: Risk of set system wise statement_timeout

Odd behavior in functions w/ anyarray & anyelement - Mailing list pgsql-general

Previous

Next