The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> support free software

5.4.10 Polymorphic SQL Functions

SQL functions can be declared to accept and return the polymorphic types anyelement, anyarray, anynonarray, and anyenum. See section 5.2.5 Polymorphic Types for a more detailed explanation of polymorphic functions. Here is a polymorphic function make_array that builds up an array from two arbitrary data type elements:

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS 
  anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 
  'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

Notice the use of the typecast 'a'::text to specify that the argument is of type text. This is required if the argument is just a string literal, since otherwise it would be treated as type unknown, and array of unknown is not a valid type. Without the typecast, you will get errors like this:

ERROR:  could not determine polymorphic type because input 
  has type "unknown"

It is permitted to have polymorphic arguments with a fixed return type, but the converse is not. For example:

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS 
  boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning a polymorphic type must have at 
  least one polymorphic argument.

Polymorphism can be used with functions that have output arguments. For example:

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 
  anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

Polymorphism can also be used with variadic functions. For example:

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS 
  anyelement AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);
 anyleast
----------
       -1
(1 row)

SELECT anyleast('abc'::text, 'def');
 anyleast
----------
 abc
(1 row)

CREATE FUNCTION concat(text, VARIADIC anyarray) RETURNS text 
  AS $$
    SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;

SELECT concat('|', 1, 4, 2);
 concat
--------
 1|4|2
(1 row)