Odd behavior in functions w/ anyarray & anyelement

Поиск
Список
Период
Сортировка
От Joshua Burns
Тема Odd behavior in functions w/ anyarray & anyelement
Дата
Msg-id CAB73KvQt5jkCg+tmNL_cpE6RpD=Df2Nz2JVOFvUN6fUQc7w6Hw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Odd behavior in functions w/ anyarray & anyelement  (David Johnston <polobo@yahoo.com>)
Re: Odd behavior in functions w/ anyarray & anyelement  (David Johnston <polobo@yahoo.com>)
Re: Odd behavior in functions w/ anyarray & anyelement  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

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 по дате отправления:

Предыдущее
От: David Johnston
Дата:
Сообщение: Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
Следующее
От: Alex Lai
Дата:
Сообщение: Risk of set system wise statement_timeout