How to create a stored function so that it can return different types?

Question

PostgreSQL

In the returned table, the second column is of type INTEGER:

CREATE OR REPLACE FUNCTION foo(str VARCHAR)
RETURNS TABLE (key INTEGER, value INTEGER) AS $$

DECLARE 

  --
      
BEGIN
    
  --
  
END;

$$ LANGUAGE plpgsql;

In the returned table, the second column of type FLOAT:

CREATE OR REPLACE FUNCTION foo(str VARCHAR)
RETURNS TABLE (key INTEGER, value FLOAT) AS $$

DECLARE 

  --
      
BEGIN
    
  --
  
END;

$$ LANGUAGE plpgsql;

Answer 1

It is possible, but there is an inconvenience when calling such functions:

CREATE OR REPLACE FUNCTION public.foo(str character varying)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $$
BEGIN
  IF str = 'i' THEN
   RETURN QUERY SELECT i, i*i FROM generate_series(1, 10) i;
  ELSE
    RETURN QUERY SELECT i, SQRT(i::float) FROM generate_series(1, 10) i;
  END IF;
END;
$$

# select * from foo('i') as (key int, value int);
 key | value
-----+-------
   1 |     1
   2 |     4
   3 |     9
   4 |    16
   5 |    25
   6 |    36
   7 |    49
   8 |    64
   9 |    81
  10 |   100
(10 rows)

# select * from foo('x') as (key int, value float);
 key |       value
-----+--------------------
   1 |                  1
   2 | 1.4142135623730951
   3 | 1.7320508075688772
   4 |                  2
   5 |   2.23606797749979
   6 |  2.449489742783178
   7 | 2.6457513110645907
   8 | 2.8284271247461903
   9 |                  3
  10 | 3.1622776601683795
(10 rows)