The wonders of Any Element

PostgreSQL has this interesting placeholder called anyelement which it has had for a long time and its complement anyarray. They are used when you want to define a function that can handle many types arguments or can output many types of outputs. They are particularly useful for defining aggregates, which we demonstrated in Who's on First and Who's on Last and several other aggregate articles.

Anyelement / anyarray can be used just as conveniently in other functions. The main gotcha is that when you pass in the first anyelement/anyarray all subsequent anyelement / anyarray must match the same data type as the first anyelement / anyarray.

Anyelement in more than 1 slot

Let us say I had a function that can take in as input any type and return a record with a column that can be any type. A perfect example, I want to create a function that takes 2 elements of the same type, gives me the difference, and returns back the original elements incremented by 1.

CREATE OR REPLACE FUNCTION diff_inc(IN anyelement, IN anyelement
  , OUT diff integer, OUT f_val anyelement, OUT l_val anyelement)
RETURNS record
AS
$$
SELECT ($1 - $2)::integer As diff, $1 + 1 As f_val, $2 + 1 As l_val;
$$
language 'sql' IMMUTABLE STRICT;


-- legal --
SELECT (diff_inc('2011-12-31'::date, '2012-01-05'::date)).*;

-- output --
diff |   f_val    |   l_val
-----+------------+------------
  -5 | 2012-01-01 | 2012-01-06
-- legal --
SELECT (diff_inc(1, 2)).*;

-- output --
 diff | f_val | l_val
------+-------+-------
   -1 |     2 |     3
  
-- not legal --
SELECT (diff_inc('2011-12-31'::date, 5)).*;

-- gives error --
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
ERROR:  function diff_inc(date, integer) does not exist

-- legal --
SELECT (diff_inc(2.5, 2.5)).*;

-- gives --
 diff | f_val | l_val
------+-------+-------
    0 |   3.5 |   3.5
    
-- not legal --
SELECT (diff_inc(2, 2.5)).*;
-- gives error --
ERROR:  function diff_inc(integer, numeric) does not exist
LINE 1: SELECT (diff_inc(2, 2.5)).*;

RETURNS TABLE with anyelement

In a prior article we said that RETURNS TABLE and OUT are essentially the same. I was thinking they might behave differently with anyelement. In fact they work the same, so if you prefer the RETURNS TABLE syntax instead of using OUT params, you can define the above function like:

CREATE OR REPLACE FUNCTION diff_inc2(IN anyelement, IN anyelement)
RETURNS TABLE(diff integer, f_val anyelement, l_val anyelement)
AS
$$
SELECT ($1 - $2)::integer As diff, $1 + 1 As f_val, $2 + 1 As l_val;
$$
language 'sql' IMMUTABLE STRICT;