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