The wonders of Any Element   

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;